Trying to optimise DAX to not send two SQL queries

[Anonymous]

Hi everyone, I'm hoping someone might be able to help me with this question. We're trying to optimise our Power BI reports as we have a lot of data so any speed increases we can find would be very useful.

[Warning - long post!]

We are displaying a KPI card. The KPI card shows an actual sales figure and for the target goal, we have a measure which selects different data depending on what value a slicer is set to.

Here’s the code for one such measure:
kpi_comparison_target_item =
SWITCH (
VALUES ( plan_lfl_switcher[Comparison] ),
"Plan",
CALCULATE (
SUM ( site_category_date_metrics[items_sold_planned] ), 
site_category_date_metrics[reporting_category_name] IN { "Category1" }
),
"LYR",
CALCULATE (
SUM ( site_category_date_metrics[items_sold_last_year] ),
site_category_date_metrics[reporting_category_name] IN { "Category1" }
)
)

So this code checks which value is selected in plan_lfl_switcher[comparison] (Plan or LYR) and then decides which dax to execute.

For some reason, when it performs the switch lookup, the KPI card's dax seems to split the SQL into two queries - one for the actual sales figure, one for whichever target goal has been selected.

If I change the code to the following to force Plan (or LYR), only one SQL query is sent which returns both the actual sales figure and target goal together. It is also a bit quicker. Because we have 8 of these KPI cards on a page, even a half second increase in speed equates to a fairly meaningful time saving

kpi_comparison_target_item =
SWITCH (
"Plan",
"Plan",
CALCULATE (
SUM ( site_category_date_metrics[items_sold_planned] ),
site_category_date_metrics[reporting_category_name] IN { "Category1" }
),
"LYR",
CALCULATE (
SUM ( site_category_date_metrics[items_sold_last_year] ),
site_category_date_metrics[reporting_category_name] IN { "Category1" }
)
)

Using SELECTEDVALUE(plan_lfl_switcher[Comparison] ) instead of VALUES( plan_lfl_switcher[Comparison] ) results in the same issue.

Can anyone advise how to switch on the selectedvalue of a slicer and prevent the Power BI KPI card spitting out two SQL queries?

Thanks in advance!

[Anonymous]

Unless I'm missing something, I assume there no way of having e.g. a couple of buttons on the dashboard. Clicking one would set a variable to "Plan", the other would set that same variable to "LYR".  Then we could reference that variable in the above Dax rather than looking at the Comparison table?

[Greg_Deckler]

@Anonymous Have you tried MAX? Although I suspect it will do the same. I assume this is DirectQuery. So, if you think about it, you are getting values from two different tables so of course that is generally going to result in two SQL queries. You are first getting data from the table plan_lfl_switcher and then using that value to retrieve data from site_category_date_metrics

[Anonymous]

@Greg_Deckler  Unfortunately, you're correct - MAX doesn't fix the issue.

The site_category_date_metrics table is a DirectQuery however plan_lfl_switcher has been defined just with the following so isn't on the SQL Server

plan_lfl_switcher = {

 (1,"Plan"),

 (2,"LYR")

}

[parry2k]

@Anonymous how about if you save the value in a variable first and then check it in switch

kpi_comparison_target_item =
VAR __comparison = SELECTEDVALUE ( plan_lfl_switcher[Comparison] )
RETURN
SWITCH (
__comparison,
"Plan",
CALCULATE (
SUM ( site_category_date_metrics[items_sold_planned] ), 
site_category_date_metrics[reporting_category_name] IN { "Category1" }
),
"LYR",
CALCULATE (
SUM ( site_category_date_metrics[items_sold_last_year] ),
site_category_date_metrics[reporting_category_name] IN { "Category1" }
)
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k ,

Thanks for your suggestion.  Unfortuantely, I have tried this method already and it doesn't make a difference.  Very annoying!