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.
ID Category Sub-category Level
111 A ! 1
111 A ? 1
111 B / 1
111 B ! 1
111 C ! 2
111 C ? 2
111 D ! 1
111 D ? 1
222 A ! 2
222 B ! 1
222 B ? 1
222 C ! 1
I want to create a column that tells you the distinct number of occurrences of ‘Level’ 1 for a given ‘ID’ based on the ‘Category’. For example, ID 111 has a level 1 for category A, B & D so I would want 3 in the new column. In essence, I want the sub-category to be ignored.
I’d want a table like this:
ID Category Sub-category Level Count of Level 1s
111 A ! 1 3
111 A ? 1 3
111 B / 1 3
111 B ! 1 3
111 C ! 2 3
111 C ? 2 3
111 D ! 1 3
111 D ? 1 3
222 A ! 2 2
222 B ! 1 2
222 B ? 1 2
222 C ! 1 2
Solved! Go to Solution.
Hi @aledc ,
Try this:
Count of Level 1s =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Category] ),
FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Level] = 1 )
)
Best regards
Icey
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Hi @aledc ,
Try this:
Count of Level 1s =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Category] ),
FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Level] = 1 )
)
Best regards
Icey
If this post helps,then consider Accepting it as the solution to help other members find it faster.
@aledc , try measure like
calculate(distinctcount(Table[Category]), allexcept(Table, Table[ID]))
or
calculate(distinctcount(Table[Category]), filter(allselected(Table), Table[ID] =max(Table[ID])))
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |