Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Optimize DAX Code

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:
LuciaZ_0-1690811471358.pngLuciaZ_1-1690811483977.png

When I filter specific agent the chart appears.

LuciaZ_2-1690811618876.png

 

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

 

1 REPLY 1
larsheinemann
Frequent Visitor

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors