topic Re: Showing Latest Data in DAX Commands and Tips

Showing Latest Data

Love2phish — Fri, 12 Apr 2024 14:16:41 GMT

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

Re: Showing Latest Data

Sahir_Maharaj — Fri, 12 Apr 2024 20:12:59 GMT

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]) )

Re: Showing Latest Data

lbendlin — Sat, 13 Apr 2024 21:06:18 GMT

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 combination

Note that I (ab)used the column total for the "Last contact date".

Re: Showing Latest Data

Love2phish — Tue, 23 Apr 2024 13:01:34 GMT

Thank you this solved it for me.