locked
DAX: Filter multiple values with COUNTAX RRS feed

  • Question

  • Hi all,

    I'm needing a COUNTAX measure that filters multiple text values.  Currently, my formula works properly for just one value ("Yes").  But I want to add a second filtered value ("Maybe").  Here is my current measure:

    Totals:= COUNTAX(FILTER('Table',[Result]="Yes"), [Result])

    I have tried using || and && with "Maybe" but cannot get the syntax right.  Suggestions?

    Thanks,

    ~UG

    Wednesday, March 5, 2014 6:01 PM

Answers

  • No problem. Using the columns in each CALCULATE parameter will combine them in an 'AND' operation. If either column should be true then you will need to re-introduce the FILTER function and use the OR operator there:

    It should look something like this...

    Totals:=
    CALCULATE(
      COUNTA('Table'[Result]), 
      FILTER(
        'Table',
        'Table'[Result]="Yes" 
          || 'Table'[Result2] = "Yes"
      )
    )


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog


    • Edited by Michael Amadi Thursday, March 13, 2014 5:07 PM
    • Marked as answer by undergrads1 Friday, March 14, 2014 4:49 PM
    Thursday, March 13, 2014 4:39 PM

All replies

  • Try

    Totals:= COUNTAX(FILTER('Table',([Result]="Yes") || ([Result]="Maybe")), [Result])


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Michael Amadi Wednesday, March 5, 2014 10:49 PM
    Wednesday, March 5, 2014 7:00 PM
  • You can use Calculate function:

    CALCULATE(<expression>,<filter1>,<filter2>…)

    e.g. Totals:= (COUNTA('Table'[Result]), [Result]="Yes", [Result] = "Maybe"))

    • Proposed as answer by Michael Amadi Wednesday, March 5, 2014 10:49 PM
    • Marked as answer by undergrads1 Wednesday, March 5, 2014 11:25 PM
    • Unmarked as answer by undergrads1 Wednesday, March 5, 2014 11:25 PM
    Wednesday, March 5, 2014 7:09 PM
  • You can use Calculate function:

    CALCULATE(<expression>,<filter1>,<filter2>…)

    e.g. Totals:= (COUNTA('Table'[Result]), [Result]="Yes", [Result] = "Maybe"))

    Both of the above DAX formulas should be equivalent in terms of results but the CALCULATE version will most likely give better performance over large datasets.

    EDIT:

    The CALCULATE version would need to be revised to:

    Totals:=
    CALCULATE(
      COUNTA('Table'[Result]), 
      'Table'[Result]="Yes" 
        || 'Table'[Result] = "Maybe"
    )

    OR

    Totals:=
    CALCULATE(
      COUNTA('Table'[Result]), 
      'Table'[Result]="Yes" 
        && 'Table'[Result] = "Maybe"
    )

    Depending on whether you require the || or && operation.


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog


    • Edited by Michael Amadi Wednesday, March 5, 2014 11:25 PM DAX formula edit
    • Marked as answer by undergrads1 Wednesday, March 5, 2014 11:25 PM
    • Unmarked as answer by undergrads1 Thursday, March 13, 2014 2:34 PM
    Wednesday, March 5, 2014 10:53 PM
  • This is great!!  

    Thank you everyone!!

    ~UG

    Wednesday, March 5, 2014 11:26 PM
  • Michael,

    I have a "part 2" to this question.  Let's say I want to add another column to this measure formula, and also search for "Yes" within it.  Let's call the second column [Result2].  Meaning, I want to count "Yes" in two separate columns within a single measure.

    This doesn't seem to work...

    Totals:=
    CALCULATE
    (
      COUNTA
    ('Table'[Result]) && 'Table'[Result2],
     
    'Table'[Result]="Yes" 
        &&
    'Table'[Result2] = "Yes"
    )

    Do I need to add a second expression (COUNTA) for my second column?

    ~UG

    Thursday, March 13, 2014 2:33 PM
  • If I've understood you correctly, off the top of my head this updated formula should work...

    Totals:=
    CALCULATE(
      COUNTA('Table'[Result]), 
      'Table'[Result]="Yes", 
      'Table'[Result2] = "Yes"
    )

    If this isn't what you meant, what is the significance of counting the values in Table[Result] as opposed to Table[Result2]?


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog

    • Marked as answer by undergrads1 Thursday, March 13, 2014 3:03 PM
    • Edited by Michael Amadi Thursday, March 13, 2014 3:03 PM
    • Unmarked as answer by undergrads1 Thursday, March 13, 2014 4:00 PM
    • Marked as answer by undergrads1 Thursday, March 13, 2014 4:01 PM
    • Unmarked as answer by undergrads1 Thursday, March 13, 2014 4:05 PM
    Thursday, March 13, 2014 2:48 PM
  • EDIT:

    I cross checked this and I don't think it's correct.  Probably due to the way I asked the question...that's my fault.  This formula is only giving me all rows that have "Yes" in both columns.

    I have two columns: [Result] and [Result2], and am trying to count (sum) all the distinct row where "Yes" occurs.  See below:

    Result Result2
    Yes No
    Yes No
    No Yes
    Yes Yes
    No Yes
    No No
    Yes Yes

    My measure should = 6....(for 6 rows that have a "Yes").  Sorry.

    ~UG



    • Edited by undergrads1 Thursday, March 13, 2014 4:03 PM
    Thursday, March 13, 2014 3:03 PM
  • No problem. Using the columns in each CALCULATE parameter will combine them in an 'AND' operation. If either column should be true then you will need to re-introduce the FILTER function and use the OR operator there:

    It should look something like this...

    Totals:=
    CALCULATE(
      COUNTA('Table'[Result]), 
      FILTER(
        'Table',
        'Table'[Result]="Yes" 
          || 'Table'[Result2] = "Yes"
      )
    )


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog


    • Edited by Michael Amadi Thursday, March 13, 2014 5:07 PM
    • Marked as answer by undergrads1 Friday, March 14, 2014 4:49 PM
    Thursday, March 13, 2014 4:39 PM
  • There we go.  That's it!  I was missing the additional filter piece.  Thank you!!

    ~UG

    Friday, March 14, 2014 4:49 PM