Reply
Regular Visitor
Posts: 22
Registered: ‎08-23-2016
Accepted Solution

Need to use COUNTROW with multiple filters

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

 

 


Accepted Solutions
Established Member
Posts: 140
Registered: ‎01-05-2016

Re: Need to use COUNTROW with multiple filters

Hi @guyinkalamazoo

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!

View solution in original post

Established Member
Posts: 140
Registered: ‎01-05-2016

Re: Need to use COUNTROW with multiple filters

I would say add them up and divide on total Smiley Happy

View solution in original post


All Replies
Established Member
Posts: 140
Registered: ‎01-05-2016

Re: Need to use COUNTROW with multiple filters

Hi @guyinkalamazoo

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!

Regular Visitor
Posts: 22
Registered: ‎08-23-2016

Re: Need to use COUNTROW with multiple filters

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

Regular Visitor
Posts: 22
Registered: ‎08-23-2016

Re: Need to use COUNTROW with multiple filters

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

Established Member
Posts: 140
Registered: ‎01-05-2016

Re: Need to use COUNTROW with multiple filters

I would say add them up and divide on total Smiley Happy

Regular Visitor
Posts: 22
Registered: ‎08-23-2016

Re: Need to use COUNTROW with multiple filters

Yep, and that seems to be working.  Thanks again