Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |