Forum Discussion
Distinct count based on column values
Need a measure to calculate Distinct count of IDs that have both Types - Type1 and Type2
Sample Data:
ID | Column2 | Column3 | Type |
1 | … | … | Type1 |
2 | … | … | Type1 |
2 | … | … | Type1 |
2 | … | … | Type2 |
1 | … | … | Type1 |
3 | … | … | Type2 |
2 | … | … | Type2 |
1 | … | … | Type1 |
4 | … | … | Type2 |
4 | … | … | Type1 |
4 | … | … | Type2 |
ID 2 and 4 have both types. so expected result is 2.
3 Replies
HI,
This is not the exact soultion but would help. It will give you output as below. Once you have it split the type & you can write DAx to calculate Distinct.
ID
Type 2 Type1 Type1 Type2 Type2 Anonymous
As a MEASURE,,one way could be
Measure = COUNTROWS ( FILTER ( VALUES ( Table1[ID] ), VAR temp = CALCULATETABLE ( VALUES ( Table1[Type] ) ) RETURN CONTAINS ( temp, [Type], "Type1" ) && CONTAINS ( temp, [Type], "Type2" ) ) )Anonymous
Another way could be
Measure 2 = COUNTROWS ( FILTER ( VALUES ( Table1[ID] ), COUNTROWS ( INTERSECT ( { "Type1", "Type2" }, CALCULATETABLE ( VALUES ( Table1[Type] ) ) ) ) = 2 ) )
