Create a Measure: Add an additional OR criteria

[Anonymous]

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!

[amitchandak]

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]

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]

@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]

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 · Accepted solution]

@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"}
)
)

))