Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
rdnguyen
Helper V
Helper V

Measure vs Linked data from other Dimension tables

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.

2 REPLIES 2
rdnguyen
Helper V
Helper V

Hi @Anonymous ,

So, I have the fact table with 635 records

rdnguyen_0-1675118298080.png

Then the model is setup

rdnguyen_1-1675118359350.png

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.

rdnguyen_2-1675119694748.png

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]

 

 

 

 

Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.