Need help with a measure. Need most recent date an event occurred.

[pharmboyrx]

Hello,

I'm having trouble writing a measure. I feel like I'm close, but I haven't been able to figure out the last part. I work for a medical office that treats HIV. I need to calculate the percentage of patients who have had their viral load measured in the last year AND had a value less than 200 at their most recent lab draw.

Where I'm getting hung up is on is grabbing the last value within the specified date range. 'dim-Date1' is my date table, 'dim-LabNames' is a dimension table for types of labs, and 'fact-Labs' is my fact table with lab results on it.

'fact-Labs' columns are:

ResultDate, PatientID, LabType, LabValue

My measure as it's currently written is below. I've tried using LASTDATE in various locations, but I'm stumped. I've searched on Reddit, EnterpriseDNA, Radacad, and anywhere else I can think of and haven't found a solution that works. I'd appreciate any feedback or ideas. Thank you.

VLSupp =

VAR VLMNum = CALCULATETABLE(
    VALUES('fact-Labs'[PatientID]),
        FILTER(ALL('dim-Date1'),
            'dim-Date1'[Dates] > MAX('dim-Date1'[Dates]) - 365 &&
            'dim-Date1'[Dates] <= MAX('dim-Date1'[Dates])
         ),
        FILTER('dim-LabNames',
            'dim-LabNames'[LabType] = "HIV Viral Load"
         ),
        FILTER('fact-Labs',
            'fact-Labs'[LabAttributeValueNbr] < 200 &&
            LASTDATE('fact-Labs'[ResultDate])
            )
        )
VAR VLMDenom = CALCULATETABLE(
    VALUES('fact-Labs'[PatientID]),
        FILTER(ALL('dim-Date1'),
            'dim-Date1'[Dates] > MAX('dim-Date1'[Dates]) - 365 &&
            'dim-Date1'[Dates] <= MAX('dim-Date1'[Dates])
        ),
        FILTER('dim-LabNames',
            'dim-LabNames'[LabType] = "HIV Viral Load"
         ) 
    )
RETURN
DIVIDE(
    COUNTROWS(
        INTERSECT(VLMNum,VLMDenom)),
    COUNTROWS(VLMDenom)
)

[Tutu_in_YYC]

The function that you going to need is LASTNONBLANK.

Here is a blind attempt that may not work (LOL):

No of Patients Last year with value lower 200 for HIV=

CALCULATE(
  DISTINCTCOUNT[PatientID],
  FILTER(
     'fact-Labs',
     CALCULATE(
            MAX('fact-Labs'[LabAttributeValueNbr]),
            LASTNONBLANK( 'dim-Date1'[Dates], MAX('fact-Labs'[LabAttributeValueNbr]))) < 200 // Filter lastbelow 200
     &&
     'dim-Date1'[Year] = YEAR(TODAY())-1 //Filter to last year
     &&
     'dim-LabNames'[LabType] = "HIV Viral Load" //Filter to HIV
   )
)

May need to tweak that, but if there is a pbix you can share, I can have a look. 

[pharmboyrx]

Hello,

Thanks for the assistance! I tried your suggestion. I get a "A function FILTER has been used in a True/Fals expression that is used as a table filter expression. This is not allowed"

Unfortunately this workbook would contain private health information. I can't share it.

[PaulOlding]

Hi @pharmboyrx 

One approach to this would be to create a virtual table of the latest result date per patient and then use that as a filter in your CALCULATETABLEs.

VAR _CurrentDate = MAX('dim-Date1'[Date])
VAR _LatestResultPerPatient = 
CALCULATETABLE(
    ADDCOLUMNS(
        VALUES('fact-Labs'[PatientID]),
        "@LatestDate", CALCULATE(MAX('fact-Labs'[ResultDate]))
    ),
    'dim-LabNames'[LabType] = "HIV Viral Load",
    DATESBETWEEN('dim-Date1'[Date], _CurrentDate - 365, _CurrentDate)
)

It's generally a good idea to filter columns rather than whole tables.  Just referencing the column names rather than FILTER('table', 'table'[column] = ...)  will achieve that.

VAR VLMNum = 
CALCULATETABLE(
    VALUES('fact-Labs'[PatientID]),
    'dim-LabNames'[LabType] = "HIV Viral Load",
    'fact-Labs'[LabValue] < 200,
    TREATAS(_LatestResultPerPatient, 'fact-Labs'[PatientID], 'fact-Labs'[ResultDate])
    )

We're using TREATAS to add lineage to the virtual table

[pharmboyrx]

Thank you,

The calculation works in the total column on a matrix, but doesn't calculate monthly. I may not have specified that, but currently leadership wants this presented in a table and to see the value calculated for the last 12 month period as of that month so they can view progress.

I've uploaded an example. In my data set for calendar year 2021. This is just the numerator part. In my data set in calendar year 2021, there were 817 patients whose most recent viral load in the last year was under 200 as of December 2021. However, in the table only 108 is showing. That is the number of patients whose most recent result was in December 2021.

Basically in the Dec - 21 column I want to see 817 (calculated from 1/1 - 12/31). In Nov 21 I'd want to see the same value but calculated 12/1/20 - 11/30/21.

Thank you again for your help. This has been stumping me.