Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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:
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.
Solved! Go to 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]))
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |