Latest Enterprise DNA Initiatives

Using COUNTROWS but also managing many filters - DAX formula optimization

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…

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

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 =
       DemographicsData[PopulationName]="Adult MI",
                  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.