Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
97 | |
80 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |