Optimize DAX Code

Question

Hi,&nbsp;&nbsp;I have created this DAX command:&nbsp;∑ plná erózia kmeň = //: Výpočet období pre time intelligenceVAR EndDate = MAX(tbl_kalendar[datum])VAR StartDate = EDATE(EndDate,-12)+1VAR ActualYear = DATESBETWEEN(tbl_kalendar[datum],StartDate,EndDate)VAR EndDateLY = EDATE(MAX(tbl_kalendar[datum]),-12)+1VAR StartDateLY = EDATE(MAX(tbl_kalendar[datum]),-24)+1VAR 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_kmenVAR Result1 = GROUPBY(CALCULATETABLE(SELECTCOLUMNS(Data,&nbsp; &nbsp; &nbsp; &nbsp; "id_agent",data[id_agent],&nbsp; &nbsp; &nbsp; &nbsp; "id_klient",data[id_klient],&nbsp; &nbsp; &nbsp; &nbsp; "skupina_produkt",data[skupina_produkt],&nbsp; &nbsp; &nbsp; &nbsp; "dist_ext_cislo_PY",CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DISTINCTCOUNTNOBLANK(data[ext_cislo]),ALLEXCEPT(data,data[id_agent],data[id_klient],data[skupina_produkt]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TREATAS(Previous_Year,tbl_kalendar[datum]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; KEEPFILTERS(data[id_klient]&lt;&gt;BLANK() &amp;&amp; data[id_druh]=2 &amp;&amp; data[prov]&gt;0)),&nbsp; &nbsp; &nbsp; &nbsp; "sum_prov_PY",CALCULATE(VALUES(data[prov]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TREATAS(Previous_Year,tbl_kalendar[datum]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; KEEPFILTERS(data[id_klient]&lt;&gt;BLANK() &amp;&amp; data[id_druh]=2 &amp;&amp; data[prov]&gt;0)),&nbsp; &nbsp; &nbsp; &nbsp; "dist_ext_cislo_AY",CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DISTINCTCOUNTNOBLANK(data[ext_cislo]),ALLEXCEPT(data,data[id_agent],data[id_klient],data[skupina_produkt]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TREATAS(ActualYear,tbl_kalendar[datum]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; KEEPFILTERS(data[id_klient]&lt;&gt;BLANK() &amp;&amp; data[id_druh]=2 &amp;&amp; data[prov]&gt;0)),&nbsp; &nbsp; &nbsp; &nbsp; "sum_prov_AY",CALCULATE(VALUES(data[prov]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TREATAS(ActualYear,tbl_kalendar[datum]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; KEEPFILTERS(data[id_klient]&lt;&gt;BLANK() &amp;&amp; data[id_druh]=2 &amp;&amp; data[prov]&gt;0)),&nbsp; &nbsp; &nbsp; &nbsp; "rozdiel", CALCULATE(VALUES(data[prov]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TREATAS(Previous_Year,tbl_kalendar[datum]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; KEEPFILTERS(data[id_klient]&lt;&gt;BLANK() &amp;&amp; data[id_druh]=2 &amp;&amp; data[prov]&gt;0)) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; - &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CALCULATE(VALUES(data[prov]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TREATAS(ActualYear,tbl_kalendar[datum]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; KEEPFILTERS(data[id_klient]&lt;&gt;BLANK() &amp;&amp; data[id_druh]=2 &amp;&amp; data[prov]&gt;0))),&nbsp; &nbsp; TREATAS(Date_period,tbl_kalendar[datum]),&nbsp; &nbsp; KEEPFILTERS(data[id_klient]&lt;&gt;BLANK() &amp;&amp; data[id_druh]=2 &amp;&amp; data[prov]&gt;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])) &nbsp;//: 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]&lt;&gt;BLANK() &amp;&amp; [dist_ext_cislo_AY]=BLANK()),[sum_prov_PY])returnResult&nbsp;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.&nbsp;Is there any possibility to optimize this code, so I can use this command for the whole company without filtering specific id of agent?&nbsp;Thanks for help.&nbsp;Lucia&nbsp;

larsheinemann · Answer

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.

Forum Discussion

Optimize DAX Code

1 Reply

Related content

Dax code optimization

DAX performance optimization

DAX code issue

Optimize Dax Code

DAX query with SUMMARIZECOLUMNS - Optimization