Count if based on two columns and ignoring another

[aledc]

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

[Icey · Accepted 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.

[amitchandak]

@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])))