Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create a Measure: Add an additional OR criteria

Hi,
 
I created a measure that I now need to add additional criteria to.  It is perfoming correctly but I'd now like to also include these instances:  'Claim Data Pull'[Service Code] IN {"76700", "76705", "76770", "76775", "G0389"}
 
The current formula is counting distinct Member IDs where:
- Gender = Male
- Age = 65 to 75
- Dx 1 = Z87.891, F17.210, F17.211, F17.213, F17.218, F17.219, Z00.00, Z00.01 or Z00.8
 
However, I now need it to also count/include instances where:
- Gender = Male
- Age = 65 to 75
- Service Code = 76700, 76705, 76770, 76775 or G0389
 
Current Formula:
Abdominal Aortic Aneurysm Participating = (CALCULATE(DISTINCTCOUNT('Claim Data Pull'[Member ID]), FILTER('Claim Data Pull','Claim Data Pull'[Gender]="Male"),'Claim Data Pull'[Age]>=65,'Claim Data Pull'[Age]<=75,'claim data pull'[Dx 1] IN {"Z87.891", "F17.210", "F17.211", "F17.213", "F17.218", "F17.219", "Z00.00", "Z00.01", "Z00.8"}))
 
So, how do I add OR functionality stating to count the original instances, but also these instances:
'Claim Data Pull'[Service Code] IN {"76700", "76705", "76770", "76775", "G0389"}
 
It is not as easy as this:
Abdominal Aortic Aneurysm Participating = (CALCULATE(DISTINCTCOUNT('Claim Data Pull'[Member ID]), FILTER('Claim Data Pull','Claim Data Pull'[Gender]="Male"),'Claim Data Pull'[Age]>=65,'Claim Data Pull'[Age]<=75,'claim data pull'[Dx 1] IN {"Z87.891", "F17.210", "F17.211", "F17.213", "F17.218", "F17.219", "Z00.00", "Z00.01", "Z00.8"})) + (CALCULATE(DISTINCTCOUNT('Claim Data Pull'[Member ID]), FILTER('Claim Data Pull','Claim Data Pull'[Gender]="Male"),'Claim Data Pull'[Age]>=65,'Claim Data Pull'[Age]<=75,'Claim Data Pull'[Service Code] IN {"76700", "76705", "76770", "76775", "G0389"}))
 
...because there are some results with a Dx 1 match and Service Code match.  That would double count that instance.
 
 
Thank you!

 

Need Help
  • az38's avatar
    az38
    6 years ago

    Anonymous 

    try

    Abdominal Aortic Aneurysm Participating = (
CALCULATE(DISTINCTCOUNT('Claim Data Pull'[Member ID]),
FILTER('Claim Data Pull','Claim Data Pull'[Gender]="Male" && 
'Claim Data Pull'[Age]>=65 &&
'Claim Data Pull'[Age]<=75 &&
(
'claim data pull'[Dx 1] IN {"Z87.891", "F17.210", "F17.211", "F17.213", "F17.218", "F17.219", "Z00.00", "Z00.01", "Z00.8"} ||
'Claim Data Pull'[Service Code] IN {"76700", "76705", "76770", "76775", "G0389"}
)
)

))

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Inside filter use || or and &&

    Example

    CALCULATE(DISTINCTCOUNT('Claim Data Pull'[Member ID]), FILTER('Claim Data Pull','Claim Data Pull'[Gender]="Male" &&'Claim Data Pull'[Age]>=65,'Claim Data Pull'[Age]<=75 && 'claim data pull'[Dx 1] IN {"Z87.891", "F17.210", "F17.211", "F17.213", "F17.218", "F17.219", "Z00.00", "Z00.01", "Z00.8"} || 'Claim Data Pull'[Service Code] IN {"76700", "76705", "76770", "76775", "G0389"}))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I think Amitchandak's solution is pretty close but it also has an error:

       

      "Too many arguments were passed to the FILTER function. The maximum argument count for the function is 2."

       

      It looks like the || function is limited to only 2 arguments.  Since we already have an Age clause and a Dx 1 clause ... I don't this is correctly processing the Service Code clause.

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Anonymous 

    maybe

    Abdominal Aortic Aneurysm Participating = (
CALCULATE(DISTINCTCOUNT('Claim Data Pull'[Member ID]),
FILTER('Claim Data Pull','Claim Data Pull'[Gender]="Male"),
'Claim Data Pull'[Age]>=65,
'Claim Data Pull'[Age]<=75,

OR(
'claim data pull'[Dx 1] IN {"Z87.891", "F17.210", "F17.211", "F17.213", "F17.218", "F17.219", "Z00.00", "Z00.01", "Z00.8"},
'Claim Data Pull'[Service Code] IN {"76700", "76705", "76770", "76775", "G0389"}
)

))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I tried changing my formula to yours and I got the following error:

       

      "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

       

      I don't this "OR" is a recognized function in DAX unfortunately.

       

       

      • az38's avatar
        az38
        Community Champion
        6 years ago

        Anonymous 

        try

        Abdominal Aortic Aneurysm Participating = (
CALCULATE(DISTINCTCOUNT('Claim Data Pull'[Member ID]),
FILTER('Claim Data Pull','Claim Data Pull'[Gender]="Male" && 
'Claim Data Pull'[Age]>=65 &&
'Claim Data Pull'[Age]<=75 &&
(
'claim data pull'[Dx 1] IN {"Z87.891", "F17.210", "F17.211", "F17.213", "F17.218", "F17.219", "Z00.00", "Z00.01", "Z00.8"} ||
'Claim Data Pull'[Service Code] IN {"76700", "76705", "76770", "76775", "G0389"}
)
)

))