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,
I have the following formula that is not accomplishing what I actually want it to:
Measure = (CALCULATE(DISTINCTCOUNT('Claim Data'[Member ID]), Filter ('Claim Data', LEFT( 'Claim Data'[ICD10], 4) = "E11."))) + (CALCULATE(DISTINCTCOUNT('Claim Data'[Member ID]), Filter ('Claim Data', 'Claim Data'[ICD10] = "R73.09"))) + (CALCULATE(DISTINCTCOUNT('Claim Data'[Member ID]), Filter ('Claim Data', 'Claim Data'[Procedure Codes] = "G2089")))
It is currently counting the [Member ID] when the criteria is met in each field. In other words, if the Member ID is in the table multiple times, and the criteria is met for more than one portion of the formula...my measure is counting that Member ID multiple times. I want the measure to only count the Member ID once (aka: a distinct count). I need to incorporate an OR function.
For example, here is a dummy set of data:
Member ID ICD10 Procedure Codes
5007 E11. G2089
5005 E11. TA76Q
7708 Q23. 55CAW
8851 WE1. G2089
Currently, my measure is counting/calculating the following for each Member ID:
5007 = 2 (because the criteria E11. was met for the ICD10 field. It would count the Member ID for a second time because G2089 was met for the Procedure Codes field. I would want the result to be only 1...by counting the Member ID only once).
5005 = 1 (my measure comes back with a result of 1...which is correct because E11. criteria was met for the ICD10 field).
7708 = 0 (my measure comes come back with a result of 0...because no criteria was met which is correct).
8851 = 1 (my measure comes back with a result of 1...which is correct because G2089 criteria was met for Procedure Code field.
In summary...I need to somehow incorporate an OR function because the measure I currently have has the potential to double count instances where criteria are met if a Member ID is present more than once.
Can you assist me in writing this?
Thank you
Solved! Go to Solution.
Hey @AW1976NOVA ,
I guess this measure is the most performant way to combine the different conditions:
Measure =
CALCULATE(
DISTINCTCOUNT('Table'[Member ID])
, FILTER(
'Table'
, ('Table'[ICD10] IN {"E11." , "R73.09"} || 'Table'[Procedure Code] = "G2089")
)
)
At least it allows to create this simple table visual:
Hopefully, this provides what you are looking for.
Regards,
Tom
Hey @AW1976NOVA ,
I guess this measure is the most performant way to combine the different conditions:
Measure =
CALCULATE(
DISTINCTCOUNT('Table'[Member ID])
, FILTER(
'Table'
, ('Table'[ICD10] IN {"E11." , "R73.09"} || 'Table'[Procedure Code] = "G2089")
)
)
At least it allows to create this simple table visual:
Hopefully, this provides what you are looking for.
Regards,
Tom
Hi @AW1976NOVA -
Instead of OR, consider using variables and the MAX function in your measure:
Measure =
var __isE11 = CALCULATE..."E11."
var __isR73 = CALCULATE..."R73.09"
var __isG2089 = CALCULATE..."G2089"
return
MAX(__isE11, MAX(isR73, __isG2089))
Hope this helps
David
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |