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'm trying to apply a DAX expression to identify patients who have diabetes based on Blood Results.
There are two tables, Patients table and Blood Results table.
Whoever, in order for a patient to be diagnosed with diebetes, two readings should be 12 months apart.
For example, patient with Id 1 is not diabetic because there is only one reading. Patient with Id 2 IS diabetic because there are 2 readings with less than 12 months apart and both are greater than 50. Patient with Id 3 is not diabetic because the duration is more than 12 months.
Patients table
Patient Id |
1 |
2 |
3 |
Blood Results table
Patient Id | Date | Test | Result |
1 | 30/08/2019 | ALT | 12 |
1 | 30/09/2019 | HbA1c | 30 |
2 | 01/08/2019 | HbA1c | 30.5 |
2 | 01/08/2019 | HbA1c | 55 |
2 | 01/09/2019 | HbA1c | 60 |
3 | 01/08/2019 | TP | 3 |
3 | 3/08/2001 | HbA1c | 60 |
3 | 3/08/2019 | HbA1c | 61 |
This is the result of the DAX expression
Patient Id | Is Diabetic (Calculated Column) |
1 | False |
2 | True |
3 | False |
How can I write a calculated column (not a measure) on the Patient table?
Solved! Go to Solution.
Hi @Anwar
For your issue, you can do as follows:
First, create column in "Result" table:
Column = IF([Result]>50,1,0)
Column 2 = CALCULATE(
LASTNONBLANK('Blood Results'[Date],0),
FILTER(
ALLEXCEPT('Blood Results',
'Blood Results'[Patient Id]
),
'Blood Results'[Test]="HbA1c"&&'Blood Results'[Result]>50&&'Blood Results'[Date]<EARLIER('Blood Results'[Date])
)
)
Column 3 = DATEDIFF([Column 2],[Date],MONTH)<=12&&DATEDIFF([Column 2],[Date],MONTH)>0&&[Column]=1
Second, crearte column in "Patients":
Column =
var
lookvalue=LOOKUPVALUE('Blood Results'[Column 3],'Blood Results'[Patient Id],Patients[Patient Id],'Blood Results'[Column 3],TRUE())
return
IF(lookvalue=BLANK(),FALSE(),lookvalue)
Best Regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anwar
For your issue, you can do as follows:
First, create column in "Result" table:
Column = IF([Result]>50,1,0)
Column 2 = CALCULATE(
LASTNONBLANK('Blood Results'[Date],0),
FILTER(
ALLEXCEPT('Blood Results',
'Blood Results'[Patient Id]
),
'Blood Results'[Test]="HbA1c"&&'Blood Results'[Result]>50&&'Blood Results'[Date]<EARLIER('Blood Results'[Date])
)
)
Column 3 = DATEDIFF([Column 2],[Date],MONTH)<=12&&DATEDIFF([Column 2],[Date],MONTH)>0&&[Column]=1
Second, crearte column in "Patients":
Column =
var
lookvalue=LOOKUPVALUE('Blood Results'[Column 3],'Blood Results'[Patient Id],Patients[Patient Id],'Blood Results'[Column 3],TRUE())
return
IF(lookvalue=BLANK(),FALSE(),lookvalue)
Best Regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |