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: 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
Can this measure be optimized in speed?
I have this measure that is used to rank customers. In the data model it is 4 different measure, and the final ranking / priority measure is this: Prio = IF ( [RFV DM] > 1, 1, IF ( [Historisk god hitrate] > 0 && [RFV DM] <= 1, 2, IF ( [Lotteri spillere] > 0, 3, IF ( [Doner minus brev] > 0, 4,0)))) Where RFV DM = VAR RFV = CALCULATE( ([Direct mail Bidrag])/([Antal Direct Mails]*Brevpris[Værdien Brevpris]) ) RETURN IF( [Antal Direct Mails] > 0, RFV, BLANK()) Historisk god hitrate = VAR MinAntalBidrag = MIN( 'StøttebrevePrio2'[Antal støttebreve prioritet 2] ) VAR MaksAntalBidrag = MAX( 'StøttebrevePrio2'[Antal støttebreve prioritet 2] ) VAR MinAntalLotteri = MIN( LotteribrevePrio2[Antal lotteribreve prioritet 2] ) VAR MaksAntalLotteri = MAX( LotteribrevePrio2[Antal lotteribreve prioritet 2] ) VAR MailSum = IF ( [Antal Direct Mails] >= MinAntalBidrag && [Antal Direct Mails] <= MaksAntalBidrag, 1, 0) VAR LotteriSum = IF ( [Antal Lotteri breve] >= MinAntalLotteri && [Antal Lotteri breve] <= MaksAntalLotteri, 1, 0) VAR DirectCnt = IF ( MailSum = 1 && LotteriSum = 1, CALCULATE( COUNTROWS( Splittest ), Splittest[DirectMail bidrag] > 0 ), 0 ) RETURN IF ( DirectCnt >= AntalDonationerPrio2[Antal donationer prio2], DirectCnt) Lotteri spillere = VAR MinAntalBidrag = MIN( 'StøttebrevePrio3'[Antal støttebreve prioritet 3] ) VAR MaksAntalBidrag = MAX( 'StøttebrevePrio3'[Antal støttebreve prioritet 3] ) VAR MinAntalLotteri = MIN( LotteribrevePrio3[Antal lotteribreve prioritet 3] ) VAR MaksAntalLotteri = MAX( LotteribrevePrio3[Antal lotteribreve prioritet 3] ) VAR MailSum = IF ( [Antal Direct Mails] >= MinAntalBidrag && [Antal Direct Mails] <= MaksAntalBidrag, 1, 0) VAR LotteriSum = IF ( [Antal Lotteri breve] >= MinAntalLotteri && [Antal Lotteri breve] <= MaksAntalLotteri, 1, 0) VAR LotteriCnt = IF ( MailSum = 1 && LotteriSum = 1, CALCULATE( COUNTROWS( Splittest ), Splittest[Lotteri bidrag] > 0 ), 0 ) RETURN IF ( LotteriCnt >= AntalDonationerPrio3[Antal donationer prio3], LotteriCnt) Doner minus brev = VAR MinAntalBidrag = MIN ( 'StøttebrevePrio4'[Antal støttebreve prioritet 4] ) VAR MaksAntalBidrag = MAX ( 'StøttebrevePrio4'[Antal støttebreve prioritet 4] ) VAR MailSum = IF ( [Antal Direct Mails] >= MinAntalBidrag && [Antal Direct Mails] <= MaksAntalBidrag, 1, 0 ) VAR AndetCnt = IF ( MailSum = 1, CALCULATE ( COUNTROWS ( Splittest ), Splittest[Andet bidrag] > 0 ), 0 ) RETURN IF ( AndetCnt >= [Antal donationer prio4], AndetCnt ) Each measure is ranking the customers under different conditions. That is why I have different variables in each measure. The measure prio makes sure that it ranks in correct order. all measures look through about 4 million plus rows, so it takes like 16-20 sec before the report returns data if a filter is changed. Also each measure takes about 4 seconds each if I look in the optimizer. Is it possible to optimize the speed though changing the DAX?Calculation Groups Very Slow
I am working on a report that uses calculation groups to let users select which time intelligence groups they would like to see (CY YTD, PY PTD, YTD Variance, CY TTM, PY TTM, TTM Variance). There is also a perspective set up to allow them to pull in certain dimensions from the dataset. This is all set up in Tabular Editor opened from the dataset. The KPIs are DAX measures within the Power BI Report itself. The dataset is a star schema and has several million records. Does anyone have any advice for how to optimize and speed up calculation groups within Power BI? Could it be related to my DAX measures being in the report instead of the DS? Any advice or things to try would be helpful. Thanks!Optimising measure to replace URL column
I have a dataset with a table containing barcodes, and I have been generating a URL from these with a simple concatenation in SQL prior to import by my Power BI dataset. e.g.: 'https://first-part-of-url.com/'||"Barcode" That links to the item's page on my company's internal system. It's mainly used in tables within drill-through or detail pages in my reports. All my tables have been imported, no DQ. I was looking at the size of my dataset and saw that each URL was taking about 10 - 15% of my dataset size. That makes sense given the number of unique values of that field. So I thought I'd use a measure that could be used in those tables. I was aware I'd be reducing size/load speed at the expense of load times within the report. Here's the measure I drafted: Item URL = IF( HASONEVALUE('Item Summary'[Barcode]), "https://first-part-of-url.com/" & VALUES('Item Summary'[Barcode]) ) I did some benchmarking on DAX studio and on a test table query the duration of the query went from 67 ms to 1365 ms. That jump in load time is higher than I was expecting. Is there a more efficient DAX expression to replace this column with a measure? Thank you!
Suggestions to Optimize DAX RollUp and SubTotal query
Hi Team, We want to get the rollup and subtotals across different dimensions .Can you please tell if there is way to optimize it? EVALUATE var t1 = CALCULATETABLE( SUMMARIZE( 'Fact Sales', ROLLUP ( 'Customer'[Name], 'Customer'[Geography], 'Customer'[Region], 'Customer'[Industry], 'Store'[Subsidiary]), "Name total", ISSUBTOTAL ('Customer'[Name]), "Geography total", ISSUBTOTAL ('Customer'[Geography]), "Region total", ISSUBTOTAL ('Customer'[Region]), "Industry total", ISSUBTOTAL ('Customer'[Industry]), "Subsidiary total", ISSUBTOTAL ('Store'[Subsidiary]), "Metric Name", NAMEOF('Fact Sales'[Sales]), "Metric Value", [Sales]), TREATAS ({ "FY22" }, 'Date'[Fiscal Year]) ) RETURN t1 Thanks, AbhiramTune a measure that use Summarize
Hi, I have a measure that use a summarize , this query run slowly and i want to tune it : CALCULATE ( [Cnt], SUMMARIZE ( Sales, DimProduct[Product_id] ) ,SUMMARIZE ( Forecast, DimProduct[Product_id] ) ) The definition of the measure Cnt as bellow : Cnt := DISTINCTCOUNT ( Invoice[CustoID] ) There are any solution to optimize that measure ? How can i write the query differently without Summarize Thanks
Optimize DAX: Filter Date Table by Max Date of Different Table
Hi All, I am trying to filter a Date slicer visual to only allow selections up to the month previous to the max date in another table. I have two tables that I don't want the most recent month of those two tables available for selection, just the previous month and older. I made a measure which is kind of working but not returning the right results. As you can see in this image only four months are available for selection. This is my measure applied to the visual as "Show items when greater than 0" Date Slicer Limiter = --Limit date slicer to previous month from latest month date VAR maxpunchdate = MAX ( Punch_Report[DATE] ) VAR maxtemphours = MAX ( 'Temp Hours'[Invoice Date] ) VAR minmonth = -- If one max date is Oct 21 and the other Sept 21 I don't want either available only Aug 21 and older MONTH ( MIN ( maxpunchdate, maxtemphours ) ) VAR minyear = -- same as minmonth YEAR ( MIN ( maxpunchdate, maxtemphours ) ) RETURN CALCULATE ( COUNTROWS ( Dates ), VALUE(LEFT(Dates[DateInt],6)) < VALUE( (minyear ) & ( minmonth )), ALL(Dates) ) These are sample tables: Temp Hours Date Hours 9/5/2021 36 10/7/2018 35 10/14/2018 26 Punch_Report DATE TOTAL 9/25/2021 1.5 9/24/2021 5.32 9/23/2021 5.82 Date Table Sample Date Month & Year DateInt 8/3/2021 Aug-21 20210803 9/7/2021 Sep-21 20210907 10/5/2021 Oct-21 20211005 I'm pretty certain the reason it isn't working is because DAX MONTH is returning 1 for January not 01 and DateInt field always includes the 0. If anyone can help me with this or has a better and more efficient way of filtering I would greatly appreciate it! Thanks in advance!
