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
When I add my measures to the visual filters I am unable to select anything. Do they have to be calculated columns?
v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi Jansco 

If you can change measures(ie “Yes”, “no”) to calculated columns,you may use the workaound as doobie said. Second way,If you just need the count of (“Yes”, “no”),you may create two measures like below:

Count_yes =
CALCULATE (
    COUNT ( Table[Company] ),
    FILTER ( Table, [Measure_Yes/No] = "Yes" )
)

Regares

 

  • 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.