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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.