Using COUNTROWS but also managing many filters - DAX formula optimization

DAX DAX Calculations
1

Hi everyone,

Can anyone check on my sample measure below? I’m not sure how to do this with DAX… But I want the end value to be in percentage and then I’ll be comparing it to a goal percentage to get a KPI.

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

Thanks in advance…

2

Firstly definitely work on some simple formatting here. Your formula is very difficult to read and some easy updates can make a big difference.

http://portal.enterprisedna.co/courses/mastering-dax-calculations/lectures/1604690

Also a demo model would be helpful as well just to check a few things with the calculation.

See below for some ideas around solving this. I’ve made simple indentations as well to make it more readable.

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

From here some simple formatting of the measure should get the percentage.

Definitely utilise the DIVIDE function here as well. It’s much better practice to do so.

http://portal.enterprisedna.co/courses/mastering-dax-calculations/lectures/2000592

Thanks
Sam