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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.