Count If conditions

[KWelsh8144]

I'm trying to get a count to be represented in a card...

My data looks like this:

displayname	Type
test name 1	A
test name 1	B
test name 1	B
test name 1	B
test name 1	B
test name 1	C
test name 1	C
test name 1	C
test name 2	B
test name 2	B
test name 2	B
test name 2	B
test name 2	C
test name 2	D
test name 2	D

I want to get a count of the number of distinct displaynames where type = b and b is listed more than 3 times. I can't figure out the dax I need to get this..

Can anyone assist?

[v-cgao-msft · Accepted solution]

Hi @KWelsh8144 ,

Please try this measure:

Measure = 
var _t=SUMMARIZE('Table',[displayname],[Type],"Count",COUNTROWS('Table'))
return COUNTROWS(FILTER(_t,[Type]="B" && [Count]>3))

Result:

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[rsbin]

@KWelsh8144 ,

I would handle your question like this.  First create a calculated column to Count the B's per DisplayName:

CountBs = 
Var _DisplayName = MAX([displayname])
Var _Result = CALCULATE( COUNTA( [Type] ), FILTER(TestName, TestName[displayname] = _DisplayName && [Type] = "B" ))
RETURN
_Result

displaynameTypeCountBs

test name 1	A	4
test name 1	B	4
test name 1	B	4
test name 1	B	4
test name 1	B	4
test name 1	C	4
test name 1	C	4
test name 1	C	4
test name 2	B	4
test name 2	B	4
test name 2	B	4
test name 2	B	4
test name 2	C	4
test name 2	D	4
test name 2	D	4

Then create a Measure:

DisplayName_Count = CALCULATE( DISTINCTCOUNT( TestName[displayname] ),
                          FILTER( TestName, TestName[CountBs] >= 3 ))

If you really wanted, this could all be combined in one step using Variables, but this way shows you explicitly what the calculations are doing.

Regards,

[KWelsh8144]

Thanks for the response - it doesn't seem to be working; the first calculated column reads them all as '4', regardless of how many 'b's are assigned to each name. Does this have anything to do with reading the displayname as the max?