Optimize DAX Code

Anonymous — Mon, 31 Jul 2023 13:56:54 GMT

Hi,

I have created this DAX command:

∑ plná erózia kmeň =

//: Výpočet období pre time intelligence

VAR EndDate = MAX(tbl_kalendar[datum])

VAR StartDate = EDATE(EndDate,-12)+1

VAR ActualYear = DATESBETWEEN(tbl_kalendar[datum],StartDate,EndDate)

VAR EndDateLY = EDATE(MAX(tbl_kalendar[datum]),-12)+1

VAR StartDateLY = EDATE(MAX(tbl_kalendar[datum]),-24)+1

VAR Previous_Year = DATESBETWEEN(tbl_kalendar[datum],StartDateLY,EndDateLY)

VAR Date_period = DATESBETWEEN(tbl_kalendar[datum],StartDateLY,EndDate)

//: Výpočet pomocnej tabuľky, ktorá je rovnaká, ako v prípade full_erozia_kmen

VAR Result1 =

GROUPBY(

CALCULATETABLE(

SELECTCOLUMNS(Data,

"id_agent",data[id_agent],

"id_klient",data[id_klient],

"skupina_produkt",data[skupina_produkt],

"dist_ext_cislo_PY",CALCULATE(

DISTINCTCOUNTNOBLANK(data[ext_cislo]),ALLEXCEPT(data,data[id_agent],data[id_klient],data[skupina_produkt]),

TREATAS(Previous_Year,tbl_kalendar[datum]),

KEEPFILTERS(data[id_klient]<>BLANK() && data[id_druh]=2 && data[prov]>0)),

"sum_prov_PY",CALCULATE(VALUES(data[prov]),

TREATAS(Previous_Year,tbl_kalendar[datum]),

KEEPFILTERS(data[id_klient]<>BLANK() && data[id_druh]=2 && data[prov]>0)),

"dist_ext_cislo_AY",CALCULATE(

DISTINCTCOUNTNOBLANK(data[ext_cislo]),ALLEXCEPT(data,data[id_agent],data[id_klient],data[skupina_produkt]),

TREATAS(ActualYear,tbl_kalendar[datum]),

KEEPFILTERS(data[id_klient]<>BLANK() && data[id_druh]=2 && data[prov]>0)),

"sum_prov_AY",CALCULATE(VALUES(data[prov]),

TREATAS(ActualYear,tbl_kalendar[datum]),

KEEPFILTERS(data[id_klient]<>BLANK() && data[id_druh]=2 && data[prov]>0)),

"rozdiel", CALCULATE(VALUES(data[prov]),

TREATAS(Previous_Year,tbl_kalendar[datum]),

KEEPFILTERS(data[id_klient]<>BLANK() && data[id_druh]=2 && data[prov]>0))

CALCULATE(VALUES(data[prov]),

TREATAS(ActualYear,tbl_kalendar[datum]),

KEEPFILTERS(data[id_klient]<>BLANK() && data[id_druh]=2 && data[prov]>0))),

TREATAS(Date_period,tbl_kalendar[datum]),

KEEPFILTERS(data[id_klient]<>BLANK() && data[id_druh]=2 && data[prov]>0)),

[id_agent],[id_klient],[skupina_produkt],[dist_ext_cislo_PY],[dist_ext_cislo_AY],"sum_prov_PY",SUMX(CURRENTGROUP(),[sum_prov_PY]),"sum_prov_AY",SUMX(CURRENTGROUP(),[sum_prov_AY]),"rozdiel",SUMX(CURRENTGROUP(),[rozdiel]))

//: Do pomocnej tabuľky pridáme dva výpočtové stĺpce, rozdiel medzi dist_ext_cislo PY a AY, prepočítaný stĺpec rozdiel, aby v prípade záporných hodnôt bola zobrazená 0 (resp. Blank)

VAR Result2 =

ADDCOLUMNS(Result1,"rozdiel_dist_ext_cislo",[dist_ext_cislo_AY]-[dist_ext_cislo_PY])

//: Suma provízií zo zmlúv, ktoré existujú v predošlých 12M (PY) a nemajú žiadny záznam v posledných 12M (AY)

VAR Result =

SUMX(FILTER(Result2,[dist_ext_cislo_PY]<>BLANK() && [dist_ext_cislo_AY]=BLANK()),[sum_prov_PY])

return

Result

When I use this DAX command in card visualization it works perfectly, hovewer, When I use this metric in line chart with time dimension then I have this message:

When I filter specific agent the chart appears.

Is there any possibility to optimize this code, so I can use this command for the whole company without filtering specific id of agent?

Thanks for help.

Lucia

Re: Optimize DAX Code

larsheinemann — Mon, 31 Jul 2023 14:38:46 GMT

Hi Lucia,

Minimize Unnecessary Calculations:
- Reduce the complexity where possible, like avoiding repeated calculations.
- If any of the variables are constant across your data and don't change over time, calculate them separately, and then reference them.
Reduce the Amount of Data Being Processed:
- If there are any filters that can be applied before calculating (i.e., in the CALCULATETABLE function), you can reduce the amount of data that needs to be processed.
- Consider aggregating data at a higher level if detail isn't needed.
Consider Using Aggregated Tables:
- If you're performing the same calculations repeatedly over different periods, you might want to consider creating an aggregated table (e.g., using a summary table that's refreshed periodically or during data refresh). This can help shift some of the computational load from report-rendering time to data-refresh time.
Revise Calculations Where Possible:
- You may be able to revise some of the calculations to be more efficient. For example, your logic with DISTINCTCOUNTNOBLANK and VALUES inside SELECTCOLUMNS seems complex and potentially redundant.
- Some calculations might be done more efficiently using calculated columns in the Power BI data model rather than all in a single measure.

topic Optimize DAX Code in DAX Commands and Tips

Optimize DAX Code

Re: Optimize DAX Code