Optimization of Filtered Scenarios with a Disconnected Table
Hello, I have a disconnected table that acts as my slicer/filter. It is intended to interact with a related DIM table/field. This related DIM table/field may not have records for all FACT table records I am calculating measures on. Using ALLSELECTED (instead of SELECTEDVALUE) from the disconnected table is not adequate because results only include FACT records with related DIM table records, excluding FACT records with no associated DIM table records. I want to filter my FACT table if there is a selection, but ignore the disconnected table entirely if nothing is selected. I have a working approach shared below, but the performance is now an issue (I have 10+ metrics using parallel logic). Is there a more optimal way of filtering based on a disconnected table, but only if there is a selection? Bonus points if you can also help proactively address possible '(Blank)' measure results without further hampering performance. Deriving (Blank) as 0 would be fine. Thank you. Joe Disconnected table = selectedViolation FACT table = Data_ACTUAL DIM Table = Violation Detail Shift Hours = VAR selectedViol = SELECTEDVALUE( selectedViolation[Violation Category] ) RETURN IF( ISBLANK( selectedViol ) , CALCULATE( SUM( Data_ACTUAL[Shift Hours] ) ) , CALCULATE( SUM( Data_ACTUAL[Shift Hours] ) , FILTER( 'Violation Detail' , 'Violation Detail'[Violation Category] = selectedViol ) ) )