Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I've been struggling to create this measure. I was able to do this using a column, but was forced to change to a measure due to serious performance issues.
I'm probably butchering this so any help would be greatly appreciated.
Current Measure:
Duration_Days = VAR ID = MIN ( Fact[ID] ) VAR StatusReason = MIN ( Fact[Status Reason] ) VAR EarliestDate_Type2 = MIN ( Fact[Created On] ) VAR LatestOpenDate_Type2 = MAX ( Fact[Created On] ) VAR LatestClosedDate_Type2 = MAX ( Fact[Modified On] )
VAR Type1 = AVERAGE ( Fact[Days Open] )
VAR Type2_Open = CALCULATE ( COUNTROWS ( 'Date' ), ALL ( 'Date' ), 'Date'[IsWorkingDay] = TRUE, 'Date'[Date] >= EarliestDate_Type2, 'Date'[Date] <= LatestOpenDate_Type2 )
VAR Type2_Closed = CALCULATE ( COUNTROWS ( 'Date' ), ALL ( 'Date' ), 'Date'[IsWorkingDay] = TRUE, 'Date'[Date] >= EarliestDate_Type2, 'Date'[Date] <= LatestClosedDate_Type2 )
RETURN IF ( ID = "Type1", Type1, IF ( ID = "Type2", IF ( StatusReason = "Closed" || StatusReason = "Approved" || StatusReason = "Denided" || StatusReason = "Failed to Provide Additional Documentation", Type2_Closed, Type2_Open ) ) )
Solved! Go to Solution.
I was able to query fold this calculation into power query, which drastically improved performance. Using the Invoke Custom Function helped me rewrite the logic to create a new column in power query.
I was able to query fold this calculation into power query, which drastically improved performance. Using the Invoke Custom Function helped me rewrite the logic to create a new column in power query.
Are you able to provide some sample data
Proud to be a Super User!
The original dataset has almost 5M rows, so I reduced it down to something more manageable. Thanks very much in advance.
Sample Data Here.
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |