Forum Discussion
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 |
| Ted | b | a | a |
| Frank | c | c | c |
| Wes | b | a | a |
I want to filter out only employees which changed their team during (at least) last month. Date will be dynamic so I want to avoid typing specific date in formula.
I used this formula (I found it in this forum) but it didnt help.
| Team Changes = |
| var _CurrentDate= |
| max('table'[Date]) |
| var _Currenttime=max('table'[Team]) |
| var _Changes= |
| CALCULATE(max('table'[Team]),'team'[Date]<_CurrentDate,ALLEXCEPT('table','table'[Employee])) |
| return |
| if(hasonevalue('table'[Employee]),if(not ISBLANK(_Changes)&&_Currenttime<>_Changes, |
| _Changes & " to "& _Currenttime |
| )) |
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.
3 Replies
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.
