Forum Discussion

Jansco's avatar
Jansco
Helper I
7 years ago
Solved

Getting a count for a table visual

I need a count for a table visual. My table is set up as follows. The columns are customer, sales, date, salesover10inpreviousmonth and salesinpast5days. The last two items are both measures that b...
Need Help
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    Do you mean that you want a count of rows where [Measure1] = "Yes" and [Measure2] = "No", or do you want a count of rows where [Measure1] is not the same as [Measure2]?  (e.g. you want to count where Measure1 is yes & Measure2 is no,  AND where Measure1 is No & Measure2 is Yes in an XOR manner)

    Whatever way you want to set this up, it's mostly the same syntax.  Just chain different conditions together with && to satisfy both conditions, and chain them with || to satisfy one or the other. 

    CALCULATE (
    COUNT ( Table[Company] ),
    FILTER ( Table, [Measure1_Yes/No] = "Yes"  && [Measure2_Yes/No] = "No")
)

    Or if you're trying to do an XOR:

     

    CALCULATE (
    COUNT ( Table[Company] ),
    FILTER ( Table, [Measure1_Yes/No] <> [Measure2_Yes/No])
)

    You can also use COUNTAX syntax to avoid using the CALCULATE term.  With DAX, there's always a ton of ways to skin the cat, you just have to use the one that makes sense to you.

     

Jansco's avatar
Jansco
Helper I
7 years ago
Hi. How would I get a count of both Yes and know (i.e. one measure should equal “Yes” and the other “no”. I want a count of results that satisfy BOTH conditions. Thank you!
Cmcmahan's avatar
Cmcmahan
Resident Rockstar
7 years ago

Do you mean that you want a count of rows where [Measure1] = "Yes" and [Measure2] = "No", or do you want a count of rows where [Measure1] is not the same as [Measure2]?  (e.g. you want to count where Measure1 is yes & Measure2 is no,  AND where Measure1 is No & Measure2 is Yes in an XOR manner)

Whatever way you want to set this up, it's mostly the same syntax.  Just chain different conditions together with && to satisfy both conditions, and chain them with || to satisfy one or the other. 

CALCULATE (
    COUNT ( Table[Company] ),
    FILTER ( Table, [Measure1_Yes/No] = "Yes"  && [Measure2_Yes/No] = "No")
)

Or if you're trying to do an XOR:

 

CALCULATE (
    COUNT ( Table[Company] ),
    FILTER ( Table, [Measure1_Yes/No] <> [Measure2_Yes/No])
)

You can also use COUNTAX syntax to avoid using the CALCULATE term.  With DAX, there's always a ton of ways to skin the cat, you just have to use the one that makes sense to you.