Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |