Optimize DAX Query

[cchilton]

I am calculating the percentage a user is above or below target. Below are example measures that mimick what I am doing in Power BI. Is there any way I can avoid using a SWITCH statement in averageKPI and targetKPI? I have about 60 variables for these SWITCH statements. The targetPct measure is being used in a card. That card takes 5+ seconds to load and even longer where it is embedded in our web application. 

Thank you!

What I tried: 

averageKPI = 

// VAR metric = SELECTEDVALUE('values'[value_fields])

// RETURN

// LOOKUPVALUE(

//     TEST_Measure_Lookup_Actual[Measures],

//     TEST_Measure_Lookup_Actual[value_fields], metric

// )

What I am currently doing: 

averageKPI =
VAR metric =
SELECTEDVALUE ( 'values'[value_fields] )
VAR carInv = [carInv_measure]
VAR toyInv = [toyInv_measure]
VAR bearInv = [bearInv_measure]
etc..

VAR RESULT =
SWITCH (
TRUE (),
metric = NAMEOF('measure_tbl'[carInv_measure]), carInv,
metric = NAMEOF('measure_tbl'[toyInv_measure]), toyInv,
metric = NAMEOF('measure_tbl'[bearInv_measure]), bearInv,
etc..
)
RETURN
RESULT

targetKPI =
VAR metric =
SELECTEDVALUE ( 'targets'[targets_fields] )
VAR carInv = [carInv_measure_target]
VAR toyInv = [toyInv_measure_target]
VAR bearInv = [bearInv_measure_target]
etc..

VAR RESULT =
SWITCH (
TRUE (),
metric = NAMEOF('measure_tbl'[carInv_measure_target]), carInv,
metric = NAMEOF('measure_tbl'[toyInv_measure_target]), toyInv,
metric = NAMEOF('measure_tbl'[bearInv_measure_target]), bearInv,
etc..
)
RETURN
RESULT

targetPct =

VAR KpiActual = [averageKPI]

VAR KpiTarget = [targetKPI]

VAR Result =

    DIVIDE ( KpiActual - KpiTarget, KpiTarget )

RETURN

"(" & FORMAT(Result,"0.00%") & ")"

[mark_endicott]

@cchilton - You need to un-pivot the table that has your data in them so that you end up with 1 column each for the Actual and Targets and an additional column that defines whether those figures are CarInv, ToyInv or BearInv etc (call this measure name). 

Then you only need to create one DAX measure which will be DIVIDE ( KpiActual - KpiTarget, KpiTarget ). A slicer for the measure name column will filter out all the rows & values that are not needed for the calculation. 

I wouldnt try to solve this with DAX it's a modelling issue. Here is an article I wrote on un-pivoting data: https://triangle.im/power-bi-mistake-5-why-pivoted-data-leads-to-power-bi-pain/

If this helps, please mark it as a solution for others - it helps with visibility.

[cchilton]

Hello, I am using field parameters for SELECTEDVALUE ( 'values'[value_fields] ) and SELECTEDVALUE ( 'targets'[targets_fields] ). That's why I can't seem to find a way around using these long painful SWITCH statements. I tried using calculation groups too but I can't get that to work either. 

[mark_endicott]

@cchilton - Yep, if you have 60 different variables a field parameter will be a really bad idea. It will take a long time to code up in DAX. 

A calculation group could be less time to code up, but it could also be equal - it largely depends how you have set your tables up and named the measures. 

I honestly think you'd be better off returning to how your model has been set up and fixing things there. If your model had a column to filter the Actual and Total values, you would not need 60 different measures for Actual and Target. You would need one for each column, also your problems with slow loading of visuals will disappear. 

[cchilton]

Below is how my model is set up in Power BI.  I am using Direct Query mode. I have other virtual tables connected to different power query parameters for filtering purposes. I have one table that holds all of my calculations/measures as well. I do not understand how I can change this to make it more effective?  Should I create a table back at the data source that holds all of my calculations rather than doing them all in DAX? I have thought about doing this for awhile but I wasn't sure if it was the right way to go. Thank you for any help you are able to provide!!! (the one table called "calculation table" is just one calculation that I had to do at the data source, it would not work with DAX in direct query mode) 

[v-linyulu-msft]

Thanks for the reply from mark_endicott , please allow me to provide another insight:

Hi,@cchilton 
Regarding the issue you raised, my solution is as follows:

1.First, you can use aggregation tables: create one or more aggregation tables that contain pre-computed summary data. This reduces the amount of computation when querying.

Here's the documentation:
Aggregation to speed up the performance of a Power BI report even if all IMPORTED - RADACAD
 

2.Second, use automatic aggregations: Power BI provides automatic aggregation capabilities that automatically create and maintain aggregation tables based on query patterns to optimize performance.

Here's a screenshot of the documentation:

Automatic aggregations overview - Power BI | Microsoft Learn
 

3.Finally, there are official links related to performance optimization, I hope it will be helpful to you:

Optimization guide for Power BI - Power BI | Microsoft Learn
Troubleshoot report performance in Power BI - Power BI | Microsoft Learn

Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mark_endicott]

@cchilton - I'm assuming you need to write up to 60 different measures because of all the separate "Target" columns I see in the "company_targets" table. If this is true, this is the table that needs to be un-pivoted, although as you're using DQ this should be done within the database rather than in Power BI. 

I can't provide any more guidance until I see the DAX you are using for the [carInv_measure], [toyInv_measure] and [bearInv_measure] measures - this will give me an idea if you can do something similar with the table that these measures work off of. 

As an aside, I see you have 3 tables which could be interpreted as containing dates, "date_dimension", "Quarters" and "GroupedDates" in an ideal world and to optimise your model, these should be rolled into one table. Furthermore, this model is Snowflaked, with lots of dimensions being connected to other dimensions and sometimes with 1-to-1 relationships. Again to optimise this model, and to fit with Power BI best practices I would advise you to unite this tables at source to make this a more traditional Star Schema, it will make your DAX mush easier as well as allowing for more optimal queries being sent to your datasource.