Manually assign categories to sub-categories over multiple columns

[tonyclifton]

Hello community,

I have a table structure as shown in below image (simplified):

Product,KPI1Text,KPI1Value,KPI2Text,KPI2Value,KPI3Text,KPI3Value
Product A,Medium,2,Low,1,High,3
Product B,Low,1,High,3,Medium,2

I need to manually assign categories to the columns (sub-categories) KPI1Text, KPI2Text and KPI3Text so that the categories can be used as a filter and for clustering. For example: "How many products are rated medium in Category A"

Category -> Sub-Category:

Category,Sub-Category
Category A,KPI1Text
Category B,KPI2Text
Category B,KPI3Text

I currently have no idea what setup I need to make this work.

I hope you have some tips.

Thanks alot.

[v-juanli-msft · Accepted solution]

Hi @tonyclifton 

Open edit queries,

Create code in Advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLnFU0lHyTU3JLM0FMoyA2Ce/HEgaArFHZnoGkDJWitWBK3fCrgLFDDTlUEWGuCyKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [product = _t, KPI1Text = _t, KPI1Value = _t, KPI2Text = _t, KPI2Value = _t, KPI3Text = _t, KPI3Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"product", type text}, {"KPI1Text", type text}, {"KPI1Value", Int64.Type}, {"KPI2Text", type text}, {"KPI2Value", Int64.Type}, {"KPI3Text", type text}, {"KPI3Value", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"product", "KPI1Value", "KPI2Value", "KPI3Value"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Sub_category"}, {"Value", "KPI_Text_Value"}}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Renamed Columns", {"product", "Sub_category", "KPI_Text_Value"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns1",{{"Attribute", "KPI_Value"}, {"Value", "KPI_Value_value"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each Text.Middle([Sub_category], 3, 1)= Text.Middle([KPI_Value], 3, 1)),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Category", each if [Sub_category] = "KPI1Text" then "Category A" else "Category B")
in
    #"Added Conditional Column"

Close&&apply, create measures

Measure = CALCULATE(DISTINCTCOUNT(Table1[product]),FILTER(ALLSELECTED(Table1),Table1[Category]=MAX(Table1[Category])&&Table1[KPI_Text_Value]="Medium"))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tonyclifton]

@v-juanli-msftperfect! Just what I was looking for. Thank you very much.