topic Re: Nested IF Optimization in App Development

Nested IF Optimization

Lakshminarayanan_J — Fri, 23 Sep 2022 08:03:36 GMT

Dear Experts,

Need to optimize the below query , It has been used in across pie chart, Adhoc table, Bar chart.

We have 2 different data which will be displayed in absolute (#) and percentage(%)

1.No

2.J

Expression need to optimize

if(Calc='Auto',

if(PivotContent=No(#)', $(VAuto#) ,if(PivotContent='No(%)', $(VAuto%))),

if(CalcMeasure='Autoride’,

if(PivotContent=' No(#)'', $(VAutoride#) ,if(PivotContent= ’No(%)', $(VAutoride%))),

if(CalcMeasure='Manual',

if(PivotContent=' No(#)'',$(VManual#) ,if(PivotContent=' No(%)'',$(VManual%))),

if(CalcMeasure='Fullycost',

if(PivotContent='J(#)',$(VFullycost#) ,if(PivotContent='J(%)',$(VFullycost%))),

if(CalcMeasure='AutoAdjust',

if(PivotContent='J(#)',$(VAutoAdjust#) ,if(PivotContent='J(%)',$(VAutoAdjust%))),

if(CalcMeasure='PAutocost',

if(PivotContent='J(#)',$(VAutocost#),if(PivotContent='J(%)',$(VPAutocost%))),

if(CalcMeasure='Ncost',

if(PivotContent='J(#)',$(VNcost#) ,if(PivotContent='J(%)',$(VNcost%)))

)))))))

@rubenmarin @Frank_S @marcus_sommer @Lech_Miszkiewicz @Arlind

Re: Nested IF Optimization

rubenmarin — Fri, 23 Sep 2022 08:18:22 GMT

Hi, Why Auto is 'Calc' and the others 'CalcMeasure', just a typo?

If CalcMeasure it's something selected outside, and with only one values selected, and PivotContent only has those 2 possible values, the expression can be simplified as:

if(PivotContent=No(#)' ,$(V$(=CalcMeasure)#) ,$(V$(=CalcMeasure)%) )

Re: Nested IF Optimization

Lech_Miszkiewicz — Fri, 23 Sep 2022 08:28:50 GMT

Gi @Lakshminarayanan_J

It is not clear which parts of this expressions can be moved to smart design of data model and which must be hardcoded. Looking at labels of your measure the concept seems to be similar to the one you would normally use in PnL where groupings can be done in data model layer. That would be typical to tabular view. Is this something you could use? To answer that question we would actually have to understand each of the variables components you have inside of your IF statement. We would need to understand

what is the go with # and % - is this something what is shown in the same view at the same time for different dimension values or is it always showing only # or only % at the time/
why do you have so many expressions for Autoride, Manual,Autocost etc.. are those additive measures
what are dimensions you are putting those measures agains? Could dimensions be used to actually have a single measure instead of many?

I am just guessing here - without knowing data model, requirements of how the finished view needs to look like it is pointless to guess i reckon. Maybe others will help more.