Forum Discussion
Report pages loading delay issue
Tables which has most rows:
Facts Sales: 16 M
Sellst: 25 M
1) Sellin_UC_MTD_MIX_all% =
var MTD_Sales_445_Sellin_uc = CALCULATE([Sellin Unit Cases (UC)],
FILTER(
ALL('tablename1'),
'tablename1'[columnname1] <= MAX('tablename1'[columnname1]) &&
'tablename1'[columnname2] = MAX('tablename1'[columnname2]) &&
'tablename1'[columnname3] = MAX('tablename1'[columnname3])
)
)
var Total_MTD_Sales_445_Sellin_uc = CALCULATE([Sellin Unit Cases (UC)],
ALL(tablename2[columnname4],tablename2[columnname5]),ALL(tablename3[columnname6],tablename3[columnname7],tablename3[columnname8]))
var total_percent_sellin_uc = DIVIDE(MTD_Sales_445_Sellin_uc,Total_MTD_Sales_445_Sellin_uc,0)
return total_percent_sellin_uc
2) Sellin_UC_YTD_Mix_ALL% =
var YTD_Sales_445_Sellin_uc = CALCULATE(
[Sellin Unit Cases (UC)],
FILTER(
ALL('tablename1'),
'tablename1'[columnname1] <= MAX('tablename1'[columnname1]) &&
'tablename1'[columnname3] = MAX('tablename1'[columnname3])
)
)
var Total_YTD_Sales_445_Sellin_uc = CALCULATE(
[Sellin Unit Cases (UC)],
FILTER(
ALL('tablename1'),
'tablename1'[columnname1] <= MAX('tablename1'[columnname1]) &&
'tablename1'[columnname3] = MAX('tablename1'[columnname3])
),
ALL(tablename2[columnname4],tablename2[columnname5]),ALL(tablename3[columnname6],tablename3[columnname7],tablename3[columnname8]))
var total_percent_sellin_uc = DIVIDE(YTD_Sales_445_Sellin_uc,Total_YTD_Sales_445_Sellin_uc,0)
RETURN
total_percent_sellin_uc
3) Sellin_UC_YoY % =
var sellin_uc_cytd= CALCULATE(
[Sellin Unit Cases (UC)],
FILTER(
ALL('tablename1'),
'tablename1'[columnname1] <= MAX('tablename1'[columnname1]) &&
'tablename1'[columnname3] = MAX('tablename1'[columnname3])
)
)
var sellin_uc_pytd = CALCULATE(
[Sellin Unit Cases (UC)],
FILTER(
ALL('tablename1'),
'tablename1'[columnname3] = MAX('tablename1'[columnname3]) - 1 &&
'tablename1'[columnname2] <= MAX('tablename1'[columnname2])
)
)
var sellin_uc_ytd = DIVIDE(sellin_uc_cytd,sellin_uc_pytd,0)-1
RETURN
sellin_uc_ytd
4) MTD UC Target =
var MTD_target_raw = CALCULATE(SUM('tablename4'[columnname9]),
FILTER(
ALL('tablename1'),
'tablename1'[columnname1] <= MAX('tablename1'[columnname1]) &&
'tablename1'[columnname2] = MAX('tablename1'[columnname2]) &&
'tablename1'[columnname3] = MAX('tablename1'[columnname3])
))
var MTD_target = IF(
ISBLANK(MTD_target_raw),
[3months avg sellin_UC], MTD_target_raw
)
Return
MTD_Target
5) Sellin_MTD_UC =
CALCULATE([Sellin Unit Cases (UC)],
FILTER(
ALL('tablename1'),
'tablename1'[columnname1] <= MAX('tablename1'[columnname1]) &&
'tablename1'[columnname2] = MAX('tablename1'[columnname2]) &&
'tablename1'[columnname3] = MAX('tablename1'[columnname3])
)
)
6) Sellin_MTD UC Achievement % = DIVIDE([Sellin_MTD_UC],[MTD UC Target],0)
7) Total Month UC Target =
var mtd_target_raw = CALCULATE(SUM('tablename4'[columnname9]),
FILTER(
ALL('tablename1'),
'tablename1'[columnname2] = MAX('tablename1'[columnname2]) &&
'tablename1'[columnname3] = MAX('tablename1'[columnname3])
))
var sellin_uc_target = IF(
ISBLANK(MTD_target_raw),
[3months avg sellin_UC], MTD_target_raw
)
return
sellin_uc_target
😎 Total_Month_UC Achievement % =
DIVIDE([Sellin_MTD_UC],[Total Month UC Target],0)
9) Sellin_NSR per UC = DIVIDE(SUM('tablename5'[columnname10]),SUM('tablename5'[columnname11]),0)
10) YTD UC Target =
VAR SelectedMonth = SELECTEDVALUE('tablename1'[columnname2])
VAR SelectedYear = SELECTEDVALUE('tablename1'[columnname3])
VAR MTD_Target =
CALCULATE(
[MTD UC Target],
'tablename1'[columnname2] = SelectedMonth,
'tablename1'[columnname3] = SelectedYear
)
VAR Total_Upto_Selected_Month_Target =
CALCULATE(
[Total Month UC Target_ForYTD],
FILTER(
ALL('tablename1'),
'tablename1'[columnname2] < SelectedMonth &&
'tablename1'[columnname3] = SelectedYear
)
)
VAR Total_Previous_Months_Target = Total_Upto_Selected_Month_Target
RETURN
IF(
NOT ISBLANK(MTD_Target),
Total_Previous_Months_Target + MTD_Target,
BLANK()
)
11) Sellin_UC_YTD =
CALCULATE(
[Sellin Unit Cases (UC)],
FILTER(
ALL('tablename1'),
'tablename1'[columnname1] <= MAX('tablename1'[columnname1]) &&
'tablename1'[columnname3] = MAX('tablename1'[columnname3])
)
)
12) Sellin_YTD UC Achievement % = DIVIDE([Sellin_UC_YTD],[YTD UC Target],0)
13) Sellin_UC_YTD BTG = [YTD UC Target] - [Sellin_UC_YTD]
nandicThese are DAX mesures we are using and the tables which have the highest columns.
Hi Anonymous ,
Based on these measures, in all of them you use ALL function and then 2-3 comparisons on a row level.
Which means for each measure it is running through at least 15-25 M rows.
Ways to improve it:
1) it would be benefitial if you could have additional columns in data model (in these largest tables) which would already precalculate MAX(column1), MAX(column2), furthermore to have complete logic in these additional columns (instead of measures).
By doing it via columns, refresh may be a little bit longer, but measures will be much faster for sure.
Or at least try using variables, something like this:
Sellin_MTD_UC =
VAR maxColumn1 = MAX('tablename1'[columnname1])
VAR maxColumn2 = MAX('tablename1'[columnname2])
VAR maxColumn3 = MAX('tablename1'[columnname3])
RETURN
CALCULATE(
[Sellin Unit Cases (UC)],
'tablename1'[columnname1] <= maxColumn1,
'tablename1'[columnname2] = maxColumn2,
'tablename1'[columnname3] = maxColumn3
)
These measures with combination of ALL(table) + MAX conditions are the issue.
nandic Should I create these columns in the power bi as dax calculated columns .
Because my data semantic model mostly is coming as direct query from the data lake .
Data lake is providing some measures created over there as well .
And some measures I am creating in power bi when the ad-hoc files from SharePoint are coming.
Could you help me in this ?
Thanks a lot in advance nandic
Anonymous if these DAX measures above are in your semantic model (dataset), then you should do upgrades in that pbix file.
What i would do:
1) put each measure which contains ALL(Table) + conditions in separate card visual on the same page. Then run performance analyzer to see which is consuming the most dax time
2) then create duplicate of that measure, but using variables as mentioned above. Add this new measure to the new card visual on the page and run performace analyser again. Compare original vs this new measure to see if that has any impact on dax time (and make sure values match, that the logic didn't impact on calculation logic)
3) if it doesn't have any impact, then try adding new columns in the model for Max(column1, column2) or even further full logic if(column1>=max(column2),1,0). And then in measure just use filter(table, columnN =1...)
This is how i would approach the problem. Start from 1 measure which takes a lot of time and try to optimize it.