Group records in multiple groups under one field/column in PBI

[Anonymous]

Hello,

I'm trying to create a new column called Product Type in Power BI which has 4 groups under it:

Below are the records that I want to group in above mentioned groups so that I can create a donut chart showing the count of each individual group (i.e. 4 slices for 4 groups):

For example: Row no. 2 should be grouped in group A since it has Tag A and also in group B since it has Tag B

The final counts that all 4 groups must have under Product Type (field to be dragged on canvas for donut chart) column should be:

Is this possible in Power BI using DAX or any other available functionality?

Thanks in advance 🙂 

[Anonymous]

Thanks @bolfri for the both the solutions 🙂

I tired Soln. A and it worked as intended!

[bolfri · Accepted solution]

Record IDTags

1	Tag A, Tag B
2	Tag A, Tag B, Tag C
3	Tag B, Tag D
4	Tag A, Tag C
5	Tag C, Tag D
6	Tag A

Product TypeTag

Group A	Tag A
Group B	Tag B
Group C	Tag C
Group D	Tag D

Records = COUNTROWS(fact_table)

Solution A (DAX) =

var tags = SELECTEDVALUE(dim_tags[Tag])

return CALCULATE(

    [Records],

    FILTER(fact_table,CONTAINSSTRING(fact_table[Tags],tags)

))

Solution B (recommended):

let
Source = fact_table,
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Tags", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Tags.1", "Tags.2", "Tags.3"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Record ID"}, "Attribute", "Tags"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
#"Removed Columns"

For solution B you can use simple Records measure and it will work faster with bigger amount of data.