Forum Discussion
Showing Latest Data
Hello,
We have 5 different status we are tracking across our locations. We check one or 2 of those per month. Example shown here:
| Location | A Status | B Status | C Status | D Status | E Status | Contact Date |
| Location 1 | 1 | 2 | 3/12/2024 | |||
| Location 2 | 5 | 5 | 5 | 3 | 3/15/2024 | |
| Location 2 | 3 | 4/1/2024 | ||||
| Location 3 | 5 | 4/6/2024 | ||||
| Location 1 | 4 | 2 | 3 | 2 | 4/10/2024 |
I would like to be able to show a table with a combined latest status for each location. For example based on the table above I would like to see the following output.
| Location | A Status | B Status | C Status | D Status | E Status | Contact Date |
| Location 1 | 4 | 2 | 2 | 4/10/2024 | ||
| Location 2 | 5 | 5 | 3 | 2 | 4/1/2024 | |
| Location 3 | 5 | 4/6/2024 |
Hello Love2phish,
Can you please try this:
1. Find the most recent 'Contact Date' for each location
Latest Contact Date = CALCULATE( MAX('Table'[Contact Date]), ALLEXCEPT('Table', 'Table'[Location]) )2. For each status (A, B, C, D, E), create a measure to retrieve the latest non-blank status based on the latest contact date.
Latest A Status = CALCULATE( LASTNONBLANK('Table'[A Status], 'Table'[A Status]), 'Table'[Contact Date] = [Latest Contact Date], ALLEXCEPT('Table', 'Table'[Location]) )You may want to consider unpivoting your data.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8slPTizJzM9TMFTSUQJhBSg2gtLG+oZG+kYGRiZKsTpIykHSpkgYrBSi3BSHcgUkZTC2ib4hFtUwFaZIKiGqzbCoBrnaBNnFUDbQbAOo8lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Location " = _t, #"A Status" = _t, #"B Status" = _t, #"C Status" = _t, #"D Status" = _t, #"E Status" = _t, #"Contact Date" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Location ", "Contact Date"}, "Status ID", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " ")), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Contact Date", type date}}) in #"Changed Type"Then you can create a cross join between locations and status IDs
Table 2 = CROSSJOIN(values('Table'[Location ]),values('Table'[Status ID]))Then you can calculate the latest status for each combinationNote that I (ab)used the column total for the "Last contact date".
3 Replies
Hello Love2phish,
Can you please try this:
1. Find the most recent 'Contact Date' for each location
Latest Contact Date = CALCULATE( MAX('Table'[Contact Date]), ALLEXCEPT('Table', 'Table'[Location]) )2. For each status (A, B, C, D, E), create a measure to retrieve the latest non-blank status based on the latest contact date.
Latest A Status = CALCULATE( LASTNONBLANK('Table'[A Status], 'Table'[A Status]), 'Table'[Contact Date] = [Latest Contact Date], ALLEXCEPT('Table', 'Table'[Location]) )Thank you this solved it for me.
You may want to consider unpivoting your data.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8slPTizJzM9TMFTSUQJhBSg2gtLG+oZG+kYGRiZKsTpIykHSpkgYrBSi3BSHcgUkZTC2ib4hFtUwFaZIKiGqzbCoBrnaBNnFUDbQbAOo8lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Location " = _t, #"A Status" = _t, #"B Status" = _t, #"C Status" = _t, #"D Status" = _t, #"E Status" = _t, #"Contact Date" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Location ", "Contact Date"}, "Status ID", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " ")), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Contact Date", type date}}) in #"Changed Type"Then you can create a cross join between locations and status IDs
Table 2 = CROSSJOIN(values('Table'[Location ]),values('Table'[Status ID]))Then you can calculate the latest status for each combinationNote that I (ab)used the column total for the "Last contact date".
