Use a nested OR function within DAX

[Anonymous]

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

[TomMartens · Accepted solution]

Hey @Anonymous ,

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

[dedelman_clng]

Hi @Anonymous  - 

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