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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MarkCBB
Helper V
Helper V

Slow DAX measure

Hi There,

 

I have the below Measure, and I am pretty sure there is a better way to write this. currently this takes 3-5 mins to calculate.

I think that it is the FILTER that is slowing it down.

 

DSC 90-150 Days = DIVIDE(CALCULATE(COUNTROWS(SALES),SALES[SOH]<>BLANK(),FILTER(SALES,[Actual DSC]>=90),FILTER(SALES,[Actual DSC]<150)),CALCULATE(COUNTROWS(SALES),SALES[SOH]<>BLANK()),0)

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hey @MarkCBB, I connected with a colleague and we had a few ideas on this:

 

The Filter function is evaluating within a row context on the Sales table - ie it is evaluating the filter criteria for each row in the Sales table. There's a more efficient way we can evaluate this criteria for each row...by using a calculated column!

 

I would make a calculated column: Actual DSC = [SOH]/[Units] which gives me the Actual DSC value for each row. I can then reference this column's value to determine if I want to include the row in my measures or not.

 

Next, I would create my measure. When counting the number of rows, I would use COUNTA instead of COUNTROWS. COUNTA will count the number of rows in which a column has a non blank value.

 

DSC 150-210 = DIVIDE(CALCULATE(COUNTA(SALES[SOH]), SALES[Actual DSC] >=150 && SALES[Actual DSC]<210),COUNTA(SALES[SOH]),0)

 

So essentially COUNTA(SALES[SOH]) will count all the rows of the SALES table in which SOH is not blank.

 

Hope that helps!

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi @MarkCBB, quick question: is [Actual DSC] a measure or a column in the SALES table?

Hello @Anonymous,

 

It is a measure, below:

Actual DSC = CALCULATE(SUM(SALES[SOH])/[Total Units],SALES[SOH]<>BLANK())

And here is the onw for Total Units:

Total Units = SUM(SALES[UNITS])

Hi @Anonymous

 

This seems to work a bit faster, but still really slow:

DSC 150-210 Days = DIVIDE(CALCULATE(COUNTROWS(SALES),SALES[SOH]<>BLANK(),FILTER(SALES,[Actual DSC]>=150 && [Actual DSC]<210)),CALCULATE(COUNTROWS(SALES),SALES[SOH]<>BLANK()),0)
Anonymous
Not applicable

Hey @MarkCBB, I connected with a colleague and we had a few ideas on this:

 

The Filter function is evaluating within a row context on the Sales table - ie it is evaluating the filter criteria for each row in the Sales table. There's a more efficient way we can evaluate this criteria for each row...by using a calculated column!

 

I would make a calculated column: Actual DSC = [SOH]/[Units] which gives me the Actual DSC value for each row. I can then reference this column's value to determine if I want to include the row in my measures or not.

 

Next, I would create my measure. When counting the number of rows, I would use COUNTA instead of COUNTROWS. COUNTA will count the number of rows in which a column has a non blank value.

 

DSC 150-210 = DIVIDE(CALCULATE(COUNTA(SALES[SOH]), SALES[Actual DSC] >=150 && SALES[Actual DSC]<210),COUNTA(SALES[SOH]),0)

 

So essentially COUNTA(SALES[SOH]) will count all the rows of the SALES table in which SOH is not blank.

 

Hope that helps!

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors