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.
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
Wanted to bump this up to the top if anyone else has other ideas. Would greatly appreciate further help!
If anyone else sees this, could really use some help. Have spent a few hours trying to get this to work to no avail.
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?
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 ) )
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.
in the COUNTROWS line it should be [@outer val] not [@val]
I've also tried it without the table name and "ResultDate" within the SELECTCOLUMNS measure.
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 ) )
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?
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |