Forum Discussion
Helper IUse DAX to transpose column values based on relative date
I need to transpose or unpivot text values into separate columns based on relative date. My data looks like this:
| Unique ID | Date | Comment |
| 1 | 7/1/2019 | aaaaa |
| 1 | 8/1/2019 | bbbbb |
| 1 | 9/1/2019 | ccccc |
| 2 | 7/1/2019 | ddddd |
| 2 | 8/1/2019 | eeeee |
| 2 | 9/1/2019 | fffff |
| 3 | 7/1/2019 | ggggg |
| 3 | 8/1/2019 | hhhhh |
| 3 | 9/1/2019 | iiiii |
I need to create separate columns that return the most recent, 2nd most recent, third most recent values in the Comment column based on the Date field per Unique ID. My end result would look something like this:
| Unique ID | Most Recent Comment | 2nd Most Recent Comment | Third Most Recent Comment |
| 1 | ccccc | bbbbb | aaaaa |
| 2 | fffff | eeeee | ddddd |
| 3 | iiiii | hhhhh | ggggg |
I realize that the easiest way to address this is with the Query Editor, but unique circumstances require me to use DAX and calculated columns only.
How would I go about creating these calculated columns?
3 Replies
- v-diye-msft
Community Support
Hi TimQ
Try below M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VczLCYAwEIThXuYciBsPmlrCHnxr/w3oRNhs/tPAB1MKBAFTlJgGyd9cGDT8MDdYmUFusLEKqb/amYG7OpiBuzpZhbG/upiBu7qZgbt6GFRf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, Date = _t, Comment = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", Int64.Type}, {"Date", type date}, {"Comment", type text}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US")[Date]), "Date", "Comment") in #"Pivoted Column"Pbix attached.
TimQThanks. This solution seems like it would work but due to a weird network circumstance I am unable to transform my data table in the query editor, so my solution would have to be executed as a series of DAX calculated columns in a visual.
- v-diye-msft