Selectedvalue DAX causing 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.

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.

 

 

[jdbuchanan71]

@Anonymous 

I've not been able to find a fix for this.  My suggestion, at this point, would be to repost your original questions but include the link to your sample file.  A question with no responses will get looked at by more people and hopefully someone else will be able to help us find a solution.

Sorry I couldn't be more help.

[jdbuchanan71]

I did notice that a lot of your meausres use the Fiscal_Period_Selection table and I am wondering if you can speed it up by moving the values to the dates table.  Here are some articles on time intellegence and custom calendars that might help.

https://www.daxpatterns.com/custom-time-related-calculations/

https://ilovepowerbi.tips/2019/12/11/time-intelligence-with-custom-calendars-like-4-4-5/

[jdbuchanan71]

@Anonymous 

You have to be careful when testing speeds to avoid hitting the cache so you don't get fake speed becuase of cached results.  When I test  [Net Sales CY] with a  'Time_Frame_Selection'[Time Frame] of "Year" I get about the same results as just running  [Net Sales YTD].  

2,384,493 rows returned in about 25 seconds.

Here is the code for the DAX Studio query if you want to try it out.

EVALUATE
    SUMMARIZECOLUMNS(
    	'FACT_Invoice'[Invoice NBR]
		,'DIM_II_Item'[Item Key]
		,'DIM_II_CUST_Site_Bill_To'[Site Key (Bill)]
		--,TREATAS({"Year"}, 'Time_Frame_Selection'[Time Frame]) --Period, Quarter, Year
		--,"Net_Sales_CY", 'FACT_Invoice'[Net Sales CY]
		--,"Net Sales PTD",'FACT_Invoice'[Net Sales PTD]
		--,"Net Sales QTD",'FACT_Invoice'[Net Sales QTD]
		,"Net Sales YTD",'FACT_Invoice'[Net Sales YTD]
    )

The slowness is not coming from the SWITCH as far as I can tell.  When you use the performance analyzer in PowerBI desktop you are running into the cached results so you are better off testing in DAX Studio with the clear cache turned on.

[Anonymous]

@jdbuchanan71 thanks for taking a look. Yes when you run DAX Studio with that query you get the same because you are running it for all 1M+ rows. As I'm sure you know Power BI table visual doesn't run the query for all 1M+ rows right away. It runs the top 500 and this is where the problem seems to be (see queries below) When I create a blank page and save the work book, then open the work book (opens to the blank page), turn on performance analyzer, click on the first page it shows a 2 second difference which leads me to believe that something goofy is still going on. this would cover your concern about the "Cache" see image below for time difference:

Also check out these queries in Dax Studio. In table format in power bi it only shows the top 500 right away. Could it be that the "Switch" makes it harder to display the top 500? When I run the below in Dax Studio i am clearing the cache each time. Also check the query plans in Dax Studio. It seems that there is something definitely different that is causing the slow down. Am I missing something? Really grateful for your help.

SLOW:

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

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

  VAR __DS0PrimaryWindowed = 
    TOPN(
      502,
      __DS0Core,
      '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 __DS0FilterTable = 
    TREATAS({"Year"}, 'Time_Frame_Selection'[Time Frame])

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      
          'FACT_Invoice'[Invoice NBR],
          'DIM_II_Item'[Item Key],
          'DIM_II_CUST_Site_Bill_To'[Site Key (Bill)], 
      __DS0FilterTable,
      "Net_Sales_YTD", 'FACT_Invoice'[Net Sales YTD]
    )

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

EVALUATE
  __DS0PrimaryWindowed

[jdbuchanan71]

I can't see any problem with the calculations.  Can you put together a sample dataset that has deidentified data and post it to DropBox then share the link here?  Performance tuning without access to the model if very difficult.

[Anonymous]

@jdbuchanan71 here is a link to the dataset. I got rid of all the confidential information so all you see is keys. But the conept is the same. I look forward to hearing from you. the titles of the tables explain which is slow and which is fast. If anything does not make sense, let me know and I will response ASAP :).

LINK TO FILE:

https://www.dropbox.com/s/c5m4ytjjsugozg1/selected%20value%20slow%20just%20keys%20for%20help.pbix?dl...

Thank You!

[jdbuchanan71]

That is very strange.  When you are using the individual measures in the table, a filter on the timeframe wouldn't do anything since your base measures ([Net Sales YTD], [Net Sales QTD], [Net Sales PTD]) don't read from that table right?  

If you add all three of the base measures to the table it still runs OK?

Maybe try moving the selection to a variable like this.

Dispaly Value =
VAR _TimeFrame = SELECTEDVALUE ( Time_Frame_Selection[Time Frame], "Year" )
RETURN
SWITCH (
    _TimeFrame,
    "Year", [Net Sales YTD],
    "Quarter", [Net Sales QTD],
    "Period", [Net Sales PTD]
)

[Anonymous]

@jdbuchanan71 Thank you for helping me. I still have the problem. Even when I add all 3 base measures it still is faster (see image) than if I add the 1 dynamic measure. I also tried moving the selected value to a variable which made no difference. From this:

SWITCH(

    [Selected Time Frame],

    "Period", [Net Sales PTD],

    "Quarter", [Net Sales QTD],

    "Year", [Net Sales YTD]

)

TO this:

Net Sales CY =

var _Selection = [Selected Time Frame]

RETURN

SWITCH(

    _Selection,

    "Period", [Net Sales PTD],

    "Quarter", [Net Sales QTD],

    "Year", [Net Sales YTD]

)

Here is the "selected time frame" measure which is a disconnected 1 column 3 row table as stated before:

Selected Time Frame = SELECTEDVALUE(Time_Frame_Selection[Time Frame],"year")

Base Measures are fast (see below)

[jdbuchanan71]

@Anonymous 

Since your display value is always making a selection (the default Year when nothing is selected), my bet is the problem is in one of the other measures ( [Net Sales QTD], [Net Sales PTD]), not from the SWITCH.

Dispaly Value =
SWITCH (
    SELECTEDVALUE ( Time_Frame_Selection[Time Frame], "Year" ),
    "Year", [Net Sales YTD],
    "Quarter", [Net Sales QTD],
    "Period", [Net Sales PTD]
)

Put each of the measures in your table rather than the display value and test the performance of each one to find the culprit.

[Anonymous]

@jdbuchanan71 wow thanks for the quick response. Unfotunately I tried that and all 3 measures independantly work fast. Both when the disconnected table is filtered and not filtered. It is so strange. You can see in the image below how I test each measure individually with the same dimensions so the only variable changing is the measure I use. Notice I have the time frame filter in the filter pane filtered. Then when I swap out the "Net Sales YTD" with my Dynamic Net Sales measure it goes back up to using about 3 seconds. Thoughts?