PowerBI Count all Rows With Value In Group

Question

This seems like one of the use cases for groups, but maybe I'm understanding them wrong.

I have a table that shows a count of all rows like this:

User | Completed Tasks
Bob  | 2
Jim  | 1
Pete | 1

The table it comes from looks like this:

User | Type
Bob  | A
Bob  | B
Jim  | A
Pete | C

This is very simplified - in reality there are about 80 different types - I'm hoping to get 5 of them in a group called Secondary and the rest in a group called Primary

For the example, say I want A and B to be considered "primary" and C to be secondary.

The new table would look like this:

User | Completed Tasks | Primary | Secondary
Bob  |        2        |    2    |     0
Jim  |        1        |    1    |     0   
Pete |        1        |    0    |     1

I tried creating a group of Type with 5 called Secondary and the rest called Primary, but I was having trouble figuring it out.

I just want a Count of types for that particular group based on the filtered values and everything.

Is there an easy way to do this or do I need to create a measure/calculated column?

Answer 1

I ended up solving this by creating two calculated columns.

The Dax for the primary would be a 1 for each row not in the Secondary list:

PrimaryCount = IF(table[Type] in {"C","D","E","F","G"},0,1)

The Dax for the secondary would be a 1 for each row IN the Secondary list:

SecondaryCount = IF(table[Type] in {"C","D","E","F","G"},1,0)

Then, just add those to your table values and make sure Sum is selected (the default).

I figured using groups would be easier, but I suppose this is simple enough and seems to work.

Answer 2

Another way to approach this is to create a calculated column for Group

Group = IF(table[Type] IN {"A","B"}, "Primary", "Secondary")

You can then use the Group as the columns on a matrix and count the Type column.

Note that this approach scales better if you want to break into a lot more groups. You'd likely want to use a SWITCH in that case like this:

Group = 
    SWITCH(TRUE(),
        Table1[Type] IN {"A","B"}, "Primary",
        Table1[Type] IN {"C"}, "Secondary",
        Table1[Type] IN {"D", "E", "F"}, "Tertiary",
        "Other"
    )