Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

 

2 ACCEPTED SOLUTIONS
SabineOussi
Skilled Sharer
Skilled Sharer

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!

View solution in original post

I would say add them up and divide on total 🙂

View solution in original post

5 REPLIES 5
SabineOussi
Skilled Sharer
Skilled Sharer

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
Not applicable

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

I would say add them up and divide on total 🙂

Anonymous
Not applicable

Yep, and that seems to be working.  Thanks again

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors