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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 @v-shex-msft ,

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]

 

 

 

 

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors