Forum Discussion
Convert a Column to a measure
Hi there
I'm wondering if there is some advice out there for turning this caculated column into a measure. I want to do this because my current set up causes for a really really slow process time.
The intention of the column is to sum up the number of business days between the start and finish dates at a record level.
Depending on that result the record either passes or fails expectations.
Business Day Count = CALCULATE( SUM(Date_Dim[BusinessDayFlag]),//this is an integer returning a 1 for true FILTER( //filter intends to only count days between to and from Dim_Date, Dim_Date[Date] >=Fact_Records[RecordFromDate] && Dim_Date[Date] <=Fact_Records[RecordToDate] ) )
My attempt at making this a measure hasn't come through with the performance result. this was my attempt
Business Day Count M = CALCULATE(
SUM(Dim_Date[BusinessDayFlag]),
FILTER(
ALL(Dim_Date),
(
(
Dim_Date[FullDate] >= MIN(Fact_records[RecordFromDate]))
&& (Dim_Date[FullDate] <= MAX(Fact_records[RecordToDate])
)
)))
Hi Anonymous
You could write variable inside a measure instead of using nested measure formula
Also, not using Filter could be better for performance.
This article provides a good explaination of using Filter vs not using Filter: https://powerpivotpro.com/2010/04/quick-tip-dont-over-use-filter/
Business Day Count M = VAR mins= MIN(Fact_Records[RecordFromDate]) VAR maxs= MAX(Fact_Records[RecordFromDate]) RETURN CALCULATE( SUM(Date_Dim[BusinessDayFlag]), ALL(Date_Dim), Date_Dim[Date] >=mins && Date_Dim[Date] <=maxs )
Best Regards
Maggie
1 Reply
Hi Anonymous
You could write variable inside a measure instead of using nested measure formula
Also, not using Filter could be better for performance.
This article provides a good explaination of using Filter vs not using Filter: https://powerpivotpro.com/2010/04/quick-tip-dont-over-use-filter/
Business Day Count M = VAR mins= MIN(Fact_Records[RecordFromDate]) VAR maxs= MAX(Fact_Records[RecordFromDate]) RETURN CALCULATE( SUM(Date_Dim[BusinessDayFlag]), ALL(Date_Dim), Date_Dim[Date] >=mins && Date_Dim[Date] <=maxs )
Best Regards
Maggie