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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Tu
Regular Visitor

Slow performance when using a column from Dimension table as Measure

Hi all,

I have a Fact table and 3 dimension tables. 

Tu_0-1682621831614.png

And I need the report to be displayed in Matrix format like below.

 

DimProduction[ProductionID]DimMaterial[Material]DimDate[DateKey]TotalEventDurationProductionStartDate
1x2023-01-011:012023-12-25
1x2023-01-012:302023-12-25
2z2023-01-033:302023-12-28

The report displays data in a Matrix format, with the Matrix rows being DimProduction[ProductionID], DimMaterial[Material], and DimDate[DateKey]. The TotalEventDuration, calculated as the sum of FactEvent[Duration], is placed under values. Up to this point, the report works efficiently and produces the expected results.

 

However,  I also want to add another measure under Values, which is ProductionStartDate (calculated as MAX(DimProduction[StartDate])).  I know the logic here is not ideal because there's only 1 DimProduction[StartDate] for each DimProduction[ProductionID] since they belong to the same table . But I need DimProduction[StartDate] to be under values so that I can scroll from left to right on a Matrix, which means it needs to be summarized. (what a pain.....) 

 

Without the ProductionStartDate measure, the report generates results instantly. However, when I include the ProductionStartDate measure it takes ages to run even on smaller databases. I dont think there's any circular reference issue as it eventually completes in the end, unless ran out of resources. My hunch is that the execution plan is inefficient or problematic.

 

I am wondering if anyone has encountered a similar issue or can provide insights into what Power BI is doing in this case. Any advice or suggestions would be greatly appreciated.

 

1 REPLY 1
v-yiruan-msft
Community Support
Community Support

Hi @Tu ,

If I understand correctly, your report run very slowly after added the measure [ProductionStartDate] into the report. Am I right? What's its formula? And the following links are the ones which have the similar problem as yours, please check them. Hope they can help you resolve the problem.

Why my Power BI Matrix or Table Visual is SLOW - RADACAD

  • Performance Tune the Measure, Design a Proper Data Model
  • Consider Pre-Calculation--create custom column
  • Reduce The Number of Calculations

Solved: Slow performance of measure


I created the chart using the Date from the datedimension, and that was what ruined the performance, and makes sense. I changed the date to a precalculated date in the dimension, and used a Month-Year, a Year and a Week-Year on the x-axis, and that was part 2 of the solution.

 

I Guess that when using a simple date with a measure, the measure will try to recalculate every single possible point in the date dimension and that slowes it down ofcourse. So using a End-of-month date did the trick so to speak.


Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.