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 couldn't find anything that answered this so I thought I would ask it here. I'm trying to create a calculated column in BI that displays each patient's first visit date. I have tried LASTDATE and MAX and can't quite figure it out.
Basically, the cell needs to display the most recent visit date from a row where that meets these criteria:
Attached below is a sample data table and a screenshot of the result.
Thank you so much!
Patient ID | Visit Date | Completed | Patient Status | First Visit Date |
1 | 1/1/21 | Yes | New | |
2 | 1/2/21 | Yes | New | |
3 | 1/3/21 | No | New | |
3 | 1/4/21 | Yes | New | |
1 | 8/31/21 | Yes | Active | |
1 | 9/1/21 | Yes | Active | |
2 | 9/2/21 | Yes | Active | |
3 | 9/3/21 | Yes | Active |
Solved! Go to Solution.
First Visit Date =
MINX (
FILTER (
'Table',
'Table'[Patient ID] = EARLIER ( 'Table'[Patient ID] )
&& 'Table'[Patient Status] = "New"
&& 'Table'[Completed] = "Yes"
),
'Table'[Visit Date]
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
First Visit Date =
MINX (
FILTER (
'Table',
'Table'[Patient ID] = EARLIER ( 'Table'[Patient ID] )
&& 'Table'[Patient Status] = "New"
&& 'Table'[Completed] = "Yes"
),
'Table'[Visit Date]
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |