Manually assign categories to sub-categories over multiple columns

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.