Convert a Column to a measure

[Anonymous]

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])
)
)))

[v-juanli-msft · Accepted solution]

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