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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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