Calculate distinct count of latest test result > 9.0

[nmcclary]

Hello all,

I have a table structured in this way:

patientID    Result_Date    Value    

1                 1/1/2021         5

2                  3/3/2021         9

3                 5/5/2021          4

1                 3/1/2021          9

2                 6/1/2021          6

1                 7/1/2021          4

etc...

I need to calculate the distinct count of patients who have a value > 9 as their latest value. For this measure, it does not matter what their previous values were. I would like this to dynamically calculate based on what fiscalYear/month is chosen in the respective slicer. The Result_Date is connected to my date/time dimension table. Unfortunately per HIPAA, I cannot share the dataset.

I will be taking this number of distinct patients with a value greater than 9 as their latest value and dividing by a distinct count of all patientIDs in the respective time period to come up with a percentage.

Thank you for any assistance anyone can provide.

Thanks,

Nick

[nmcclary]

Wanted to bump this up to the top if anyone else has other ideas. Would greatly appreciate further help!

[nmcclary]

If anyone else sees this, could really use some help. Have spent a few hours trying to get this to work to no avail.

[nmcclary]

Thanks for giving this a go. I'm following what you're doing and this seems close but won't quite go.

The trouble is with the TOPN. It doesn't want to accept it as an expression in the SELECTCOLUMNS measure. I also tried it with:

SELECTCOLUMNS(TableName, "Name1", TOPN(1, .....))

Should it take the TOPN as an expression within selectcolumns? Any other ideas?

[johnt75]

my fault, forgot to add the name of the column for the ADDCOLUMNS.

Num > 9 =
VAR summaryTable =
    ADDCOLUMNS (
        VALUES ( 'Table'[patient ID] ),
        "@outer val",
            SELECTCOLUMNS (
                TOPN ( 1, 'Table', 'Table'[result date] ),
                "@val", 'Table'[Value]
            )
    )
RETURN
    COUNTROWS ( FILTER ( summaryTable, [@outer val] > 9 ) )

[nmcclary]

Seems closer but still not quite there. Here's what I have now and it won't let me pull up the "@val" column in the countrows measure. For what it's worth, the "SummaryTable" also won't pull into a calculated table. Just says "The syntax for 'ADDCOLUMNS' is incorrect. But it isn't underlined red in the calculated table DAX.

TEST =

VAR SummaryTable =

ADDCOLUMNS(VALUES(Table[patientID]), "@outer val"

, SELECTCOLUMNS(Table, "ResultDate", TOPN(1, Table, Table[Result_Date], DESC)

, "@val", Table[Value])

)

RETURN

COUNTROWS(FILTER(SummaryTable, [@val] > 9))

[@val] shows up gray here and can't be used.

[johnt75]

in the COUNTROWS line it should be [@outer val] not [@val]

[nmcclary]

I've also tried it without the table name and "ResultDate" within the SELECTCOLUMNS measure.

[johnt75]

You can try

Num > 9 =
var summaryTable = ADDCOLUMNS( VALUES('Table'[patient ID]),
SELECTCOLUMNS( TOPN(1, 'Table', 'Table'[result date]), "@val", 'Table'[Value])
)
return COUNTROWS( FILTER( summaryTable, [@val] > 9 ) )

[nmcclary]

Thanks for giving this a go. I'm following what you're doing and this seems close but won't quite go.

The trouble is with the TOPN. It doesn't want to accept it as an expression in the SELECTCOLUMNS measure. I also tried it with:

SELECTCOLUMNS(TableName, "Name1", TOPN(1, .....))

Should it take the TOPN as an expression within selectcolumns? Any other ideas?