Forum Discussion
Slow DAX measure
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!
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)
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!