Switch(SELECTEDVALUE DAX Slow performance

[Anonymous]

I have a 2 to 3 second hit on performance in this situation and I cannot figure out why. My measure is: Switch(SELECTEDVALUE(Time_Frame_Selection[Time Frame],"year"), "Year", [Net Sales YTD], "Quarter", [Net Sales QTD] "Period", [Net Sales PTD])). NOTE: the Time_Frame_Selection table is a tiny 1 column and 3 row table disconnected from the star schema data model. When I have the disconnected table column "Time Frame" unfiltered then naturally the default value "year" is selected in "Selectedvalue" and the measure above is FAST (half a second). But if I apply the filter on the column of the disconnected table in a slicer or filter pane, the exact same measure takes 2.5 seconds. The query plan completely changes and is much more complex when I filter the disconnected table column vs leaving it unfiltered. The only difference in all of this is the result based on "Selectedvalue." Naturally if I don't filter the disconnected table column then the default value is selected and it works fast. I would greatly appreciate any help. Here is a link to the .pbix file in drop box. 

https://www.dropbox.com/s/8fdal6ppewacu0a/selected%20value%20very%20slow%20just%20keys%20for%20help....

Query plans are pictured below the first is when the column is NOT filtered (first picture with shorter query plan) the second picture is when the filter on the disconnected table is applied. (second picture with longer query plan). See how much more complex the seond one is where the filter is applied.

FAST query plan when disconnected table is NOT filtered:

SLOW query plan when disconnected table is filtered:

SLOW:

 // DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"Year"}, 'Time_Frame_Selection'[Time Frame])

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP(
          'FACT_Invoice'[Invoice NBR],
          'DIM_II_Item'[Item Key],
          'DIM_II_CUST_Site_Bill_To'[Site Key (Bill)]
        ), "IsGrandTotalRowTotal"
      ),
      __DS0FilterTable,
      "Net_Sales_CY", 'FACT_Invoice'[Net Sales CY]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      502,
      __DS0Core,
      [IsGrandTotalRowTotal],
      0,
      'FACT_Invoice'[Invoice NBR],
      1,
      'DIM_II_Item'[Item Key],
      1,
      'DIM_II_CUST_Site_Bill_To'[Site Key (Bill)],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

FAST:

 // DAX Query
DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP(
          'FACT_Invoice'[Invoice NBR],
          'DIM_II_Item'[Item Key],
          'DIM_II_CUST_Site_Bill_To'[Site Key (Bill)]
        ), "IsGrandTotalRowTotal"
      ),
      "Net_Sales_CY", 'FACT_Invoice'[Net Sales CY]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      502,
      __DS0Core,
      [IsGrandTotalRowTotal],
      0,
      'FACT_Invoice'[Invoice NBR],
      1,
      'DIM_II_Item'[Item Key],
      1,
      'DIM_II_CUST_Site_Bill_To'[Site Key (Bill)],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

[amitchandak]

@Anonymous , Option, Try if you can use field parameters

https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9afd

Second, in place of measure have measure calculation itself

Switch(SELECTEDVALUE(Time_Frame_Selection[Time Frame],"year"), "Year", CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")),

"Quarter", CALCULATE(SUM(Sales[Sales Amount]),DATESQTD('Date'[Date],"12/31")),

"Period", CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

)

refer this example

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

[Anonymous]

@amitchandak thanks for the quick response. Option 2 (placing the actual measure calucation in the switch statement measure) does NOT speed things up. Same problem occurs? Option 1 (field parameters) does improve performance but is not an option because I have tons of measures relying on these disconnected tables and it would require building many filed parameter tables that would be hard to maintain. Is there a solution to improve the performance? I don't understand why this is happening? Thanks for your help, really appreciate your time.