Optimize DAX Code

[Anonymous]

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

[larsheinemann]

Hi Lucia,

1. 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.

2. 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.

3. 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.

4. 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.