topic Re: Need to use COUNTROW with multiple filters in DAX Commands and Tips

Need to use COUNTROW with multiple filters

Anonymous — Thu, 09 Feb 2017 20:25:50 GMT

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

Re: Need to use COUNTROW with multiple filters

SabineOussi — Fri, 10 Feb 2017 14:46:22 GMT

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!

Re: Need to use COUNTROW with multiple filters

Anonymous — Mon, 13 Feb 2017 12:08:31 GMT

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

Re: Need to use COUNTROW with multiple filters

Anonymous — Mon, 13 Feb 2017 13:04:29 GMT

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

Re: Need to use COUNTROW with multiple filters

SabineOussi — Mon, 13 Feb 2017 13:20:54 GMT

I would say add them up and divide on total :)

Re: Need to use COUNTROW with multiple filters

Anonymous — Mon, 13 Feb 2017 13:22:18 GMT

Yep, and that seems to be working. Thanks again