Measure vs Linked data from other Dimension tables

[rdnguyen]

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.

[rdnguyen]

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]

[v-shex-msft]

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