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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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