Hi all,
I noticed that whenever I pull in a measure with some calculation, table visual takes long time to load or not even loaded. But as soon as removed those linked data from other Dimension tables, measure immediately loaded. I am not sure what is the related linkage here regarding the issue as
- I created measure that never use data from linked tables;
- Dimension tables only pass on description for presenting visual data
- and Relation most between Dimension and Fac is 1:n
Any suggestion or experience on such observation that I brought up is highly appreciated
Thanks and best regards,
R.
Hi @v-shex-msft ,
So, I have the fact table with 635 records
Then the model is setup
Now that my measure is to either display Column A or Column B value of the fact table depending on user selection from tile slicer
Without pulling in any descriptive data form Dimension, the query run in 362 ms
Then with descriptive data from the 04 dimensions, I got to wait for about 2 mins to see data loaded on visual table.
Then I copy the query to DAX Studio to run, it showed shorter run time.
Then all at a sudden without changing anything from Dax code, the query just working faster for unknown reason.
Was that something I could do to refine my work? or any cache actually working at backend to speed up my query?
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"COMPLETED",
"RELEASED"}, 'WorkInProgress'[fstatus])
VAR __DS0FilterTable2 =
TREATAS({"Due Date"}, 'AgingCalculation'[AgingCalc])
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
'WorkInProgress'[fjobno],
'WorkInProgress'[fopen_dt],
'WorkInProgress'[fddue_date],
'WorkInProgress'[fstatus],
'WorkInProgress'[fact_rel],
'WorkInProgress'[frel_dt],
'DIM-Part-MetalList'[fdescript],
'WorkInProgress'[flastlab],
'DIM-ProductClass'[c_ProdClass],
'WorkInProgress'[c_DueDays],
'WorkInProgress'[c_ClosedD],
'WorkInProgress'[c_LeadDays],
'DIM-Part-MetalList'[c_PartRev],
'DIM-Fac-Sk'[fac]
), "IsGrandTotalRowTotal"
),
__DS0FilterTable,
__DS0FilterTable2,
"Sumfquantity", CALCULATE(SUM('WorkInProgress'[fquantity])),
"SumfnEstHrs", CALCULATE(SUM('WorkInProgress'[fnEstHrs])),
"Sumfnmatlcost", CALCULATE(SUM('WorkInProgress'[fnmatlcost])),
"Sumfnlabrcost", CALCULATE(SUM('WorkInProgress'[fnlabrcost])),
"Sumfnovhdcost", CALCULATE(SUM('WorkInProgress'[fnovhdcost])),
"Sumfnothrcost", CALCULATE(SUM('WorkInProgress'[fnothrcost])),
"Sumfnsubccost", CALCULATE(SUM('WorkInProgress'[fnsubccost])),
"Sumfntotcost", CALCULATE(SUM('WorkInProgress'[fntotcost])),
"SumfnActHrs", CALCULATE(SUM('WorkInProgress'[fnActHrs])),
"h_DueDays", 'Measure Core'[h_DueDays],
"h_LeadDays", 'Measure Core'[h_LeadDays],
"h_Aging", 'Measure Core'[h_Aging],
"Sumc_DueDays", IGNORE(CALCULATE(SUM('WorkInProgress'[c_DueDays]))),
"Sumc_LeadDays", IGNORE(CALCULATE(SUM('WorkInProgress'[c_LeadDays])))
)
VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'WorkInProgress'[fjobno],
1,
'WorkInProgress'[fopen_dt],
1,
'WorkInProgress'[fddue_date],
1,
'WorkInProgress'[fstatus],
1,
'WorkInProgress'[fact_rel],
1,
'WorkInProgress'[frel_dt],
1,
'DIM-Part-MetalList'[fdescript],
1,
'WorkInProgress'[flastlab],
1,
'DIM-ProductClass'[c_ProdClass],
1,
'WorkInProgress'[c_DueDays],
1,
'WorkInProgress'[c_ClosedD],
1,
'WorkInProgress'[c_LeadDays],
1,
'DIM-Part-MetalList'[c_PartRev],
1,
'DIM-Fac-Sk'[fac],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC,
'WorkInProgress'[fjobno],
'WorkInProgress'[fopen_dt],
'WorkInProgress'[fddue_date],
'WorkInProgress'[fstatus],
'WorkInProgress'[fact_rel],
'WorkInProgress'[frel_dt],
'DIM-Part-MetalList'[fdescript],
'WorkInProgress'[flastlab],
'DIM-ProductClass'[c_ProdClass],
'WorkInProgress'[c_DueDays],
'WorkInProgress'[c_ClosedD],
'WorkInProgress'[c_LeadDays],
'DIM-Part-MetalList'[c_PartRev],
'DIM-Fac-Sk'[fac]
HI @rdnguyen,
What type of formulas that you used? Have you nested multiple iterators in these formulas? Can you please share some more detail information about these?
How to Get Your Question Answered Quickly
You can also take a look at the following link if hlpes:
Optimization guide for Power BI - Power BI | Microsoft Learn
Optimizing nested iterators in DAX - SQLBI
Optimizing Many-to-Many Calculations in DAX with SUMMARIZE and Cross Table Filtering - SQLBI
Regards,
Xiaoxin Sheng