Need to use COUNTROW with multiple filters

[Anonymous]

I would definitely know how to do this with SQL, but not too sure with DAX.  Anyway, here is my example for a new measure:

QI24 = COUNTROWS(FILTER(DemographicsData, DemographicsData[PopulationName]="Adult MI") && filter(DemographicsData, DemographicsData[EmploymentStatusName]="1" && filter(DemographicsData, DemographicsData[EmploymentStatusName]="2" ) / COUNTROWS(FILTER(DemographicsData, DemographicsData[PopulationName]="Adult MI"))))

Obviously this is not correct.  Can someone point me in the right direction?  Ultimately what this value needs to be is a percentage in the end.  And then ultimately I will be comparing that percentage to a "goal" percentage to get a KPI.

Thanks for the information

[SabineOussi · Accepted solution]

Hi @Anonymous

Try this measure

QI24 = DIVIDE(CALCULATE(COUNTROWS(DemographicsData), DemographicsData[PopulationName]="Adult MI", DemographicsData[EmploymentStatusName]="1", DemographicsData[EmploymentStatusName]="2"), CALCULATE(COUNTROWS(DemographicsData), DemographicsData[PopulationName]="Adult MI"))

And then format it as a percentge from the modling tab.

Hope this helps!

[SabineOussi · Accepted solution]

Hi @Anonymous

Try this measure

QI24 = DIVIDE(CALCULATE(COUNTROWS(DemographicsData), DemographicsData[PopulationName]="Adult MI", DemographicsData[EmploymentStatusName]="1", DemographicsData[EmploymentStatusName]="2"), CALCULATE(COUNTROWS(DemographicsData), DemographicsData[PopulationName]="Adult MI"))

And then format it as a percentge from the modling tab.

Hope this helps!

[Anonymous]

And just one more quick question.  I see that CALCULATE filters and it probably is an implied "AND" with the filter.  I have this working, however it is coming up with Blank data.  The filter actually needs to be an "OR" expression.  Do you know how I would do that, or would I nest a few CALCULATE functions together and add them up?

Thanks

[SabineOussi · Accepted solution]

I would say add them up and divide on total 🙂

[Anonymous]

Yep, and that seems to be working.  Thanks again

[Anonymous]

Sabine,

I was out Friday so sorry to get back to you late, but THANK YOU for this syntax.  I am just now starting to learn and use DAX and this helps me greatly.

Brad