Power BI DAX - Dynamically Calculate Distinct Count of Patients with Most Recent Value Less Than 9

user_13213473 0
Viewed 51

I have a table of a1C values structured in this way:

patientID    Result_Date    Value

A            1/1/2021         5

B            3/3/2021         9

C            5/5/2021         4

A            3/1/2021         9

B            6/1/2021         13

A            7/1/2021         4

C            4/8/2022         12

etc...

I need to create a DAX measure that will dynamically calculate the distinct count of patients who have a value less than 9 as their last value as well as the distinct count of all patients in the population from the past 12 months of whatever date range is filtered. This is so I can calculate % a1c < 9. I have a calendar table in this data model so the idea is that as a user selects a fiscal month, the measure would dynamically calculate the % of distinct patients with an a1C less than 9 as their latest value over the past 12 months. I cannot share the actual dataset per HIPAA

Here are a few examples of the work I tried to do just to get a table of patients with their most recent date and most recent value as I figure that is the first step. I have been unsuccessful. Thank you for any help you can provide!

VAR summaryTable = 
    VAR patientID = VALUES(A1c[patientID])
    VAR results = FILTER(A1c, A1c[patientID] = patientID)
    VAR MaxDate = CALCULATE( MAX(A1c[Result_Date]), results)
 RETURN
 CALCULATETABLE(A1c, FILTER(results, A1c[Result_Date] = MaxDate)

/*ADDCOLUMNS(SUMMARIZE(A1c, A1c[patientID], "ValueDate", MAX(A1c[Result_Date]))
  //  , "LastValue", LOOKUPVALUE(A1c[Value_Formatted], A1c[Result_Date], FORMAT("ValueDate", "MM/DD/YYYY"), A1c[patientID], "patientID"))
// does not run due to formatting of columns; attempted to format but still doesn't work

//ADDCOLUMNS(VALUES(A1c[patientID]), "ValueDate", MAX(A1c[Result_Date]))
/*    ADDCOLUMNS (
        VALUES ( A1c[patientID] ),
        "@outer val",
            SELECTCOLUMNS (
                TOPN ( 1, 'A1c', MAX(A1c[Result_Date]) ),
                "@val", 'A1c'[Value_Formatted]
                )) */)
dax powerbi
1 Answers
lessThanNine =    

VAR patientlastDates=
        ADDCOLUMNS(
            VALUES(tbl[patientID])
            ,"lastdate",LASTDATE(tbl[Result_Date])
        )
VAR patientLastValue =
        ADDCOLUMNS(
            patientlastDates
            ,"lastValue",   VAR currentP=[patientID]
                            VAR currentD=[lastdate]
                            RETURN 
                                CALCULATE(
                                    MAX(tbl[Value])
                                    ,tbl[patientID]=currentP
                                    ,tbl[Result_Date]=currentD
                            )
        )
VAR filtered=
            FILTER(
        patientLastValue
        ,[lastValue]<9
    )
RETURN
    COUNTROWS(filtered)

Distinct count as below:

patientsWithinTimeFrame=
IF(
    ISFILTERED('tbl'[Result_Date])
    ,COUNTROWS(VALUES('tbl'[patientID]))
    ,COUNTROWS(
        CALCULATETABLE(
            VALUES('tbl'[patientID])
            ,DATESINPERIOD(
                'tbl'[Result_Date]
                ,MAX('tbl'[Result_Date])
                ,-1
                ,year
            )
        )
    )
)
user_190423790
Related