DAX: Calculation on related table with date durations

[Anwar]

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.

According to the literature, diabetes is diagnosed after TWO HbA1c readings >50mmol/mol.

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?

[v-lionel-msft · Accepted 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.