Forum Discussion
Solved
How to show only dimension changes
Hi, I created below crosstab in Power BI: Date Name Jan Feb March Jan a a a Kate a b b Ola b b b Tom c c c Mike c c a Elle a b c Ann b b b ...
First step: bring your data into usable format by unpivoting
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRSoTjWJ1oJe/EklSoQBIYgwT9cxB8mFhIfi6QnQzHIDHfzOxUJEGIia45OcgmQlQ65uVhmpiaAuUjnONWlJiXjWFPeGoxqspYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Jan = _t, Feb = _t, March = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Jan", type text}, {"Feb", type text}, {"March", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",each [Attribute],each "1-" & [Attribute] & "-24",Replacer.ReplaceValue,{"Attribute"}), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute", type date}}) in #"Changed Type1"Then you can use Power Query, or the Visual Calculations feature to highlight changes.
Either upgrade to a version that supports Visual Calculations, or implement the logic in Power Query.
First step: bring your data into usable format by unpivoting
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRSoTjWJ1oJe/EklSoQBIYgwT9cxB8mFhIfi6QnQzHIDHfzOxUJEGIia45OcgmQlQ65uVhmpiaAuUjnONWlJiXjWFPeGoxqspYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Jan = _t, Feb = _t, March = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Jan", type text}, {"Feb", type text}, {"March", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",each [Attribute],each "1-" & [Attribute] & "-24",Replacer.ReplaceValue,{"Attribute"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute", type date}})
in
#"Changed Type1"Then you can use Power Query, or the Visual Calculations feature to highlight changes.
Thank you but I don't have the same version of PB as you and cannot open your workbook. I tried create the same formula but cannot use PREVIOUS.
My data has been already unpivoted, pivot was created as visualization.
Either upgrade to a version that supports Visual Calculations, or implement the logic in Power Query.
