Getting a count for a table visual

[Jansco]

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 both return yes or no. How would I return a count of companies that fit the criteria filtered in the measures (ie “Yes”, “no”). Nothing I’ve tried so far is giving me the correct number.

[Anonymous]

How are you tracking companies/ where is this data stored; is that the customer? If the customer is the data you want to count you should be able to drag the customer column into the field for a card visual and select distinct count. This will only count each customer once. Add the measure on the visual level filter and select the category you want to count. 

[Jansco]

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]

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]

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 · Accepted solution]

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.