Create new columns with latest values based on a type from a related table

[Anwar]

I have a Dim table that represents patients and a Fact table with 4 million records that represents Blood Results.
The Blood Results table has the following columns:

• PatientId
• Date
• Type
• Value

The relationship is 1-to-many and the PatientId column is the key.
There are 3 types of Blood Tests that I want to display on the Patient table:

• ALT
• HBV DNA
• HbeAg

I want to get the latest Result for the above 3 types and show them on 3 separate columns.

Here is a small sample of the Blood Results table for six patients and their blood work:

PatientId	Date	Type	ResultValue
1	1/01/2019	ALT	10
1	25/01/2019	ALT	10.1
1	14/01/2019	HBV DNA	10.5
1	15/03/2019	HbeAg	10.66
1	29/12/2019	BIL	10.49
2	31/01/2019	ALT	2.2
2	3/11/2019	HBV DNA	3.9
2	29/07/2019	HBV DNA	2.3
3	15/06/2019	HbeAg	3.3
4	12/06/2019	BIL	4.1
4	17/06/2019	BIL	4.2
4	22/09/2019	ALT	4.9
4	8/04/2019	HbeAg	3.99
4	11/11/2019	HbeAg	4.61
4	19/06/2019	ALT	5.01
5	30/06/2019	ALT	5.6
6	25/01/2019	HBV DNA	0

This is the expected output

PatientId	Latest ALT	Latest HBV DNA	Latest HbeAg
1	10.1	10.5	10.66
2	2.2	2.3
3			3.3
4	4.9		4.61
5	5.6
6		0

I'm looking for a DAX expression that creates three column in green color as in the table above.

[MattAllington · Accepted solution]

You should be able to write a single measure and then use the column “type” you have as the column in the matrix. Something like this will work I think (not tested)

=CALCULATE(SELECTEDVALUE(FACT[value]),LASTDATE(fact[date]))