Slow performance when using a column from Dimension table as Measure

[Tu]

Hi all,

I have a Fact table and 3 dimension tables. 

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

DimProduction[ProductionID]	DimMaterial[Material]	DimDate[DateKey]	TotalEventDuration	ProductionStartDate
1	x	2023-01-01	1:01	2023-12-25
1	x	2023-01-01	2:30	2023-12-25
2	z	2023-01-03	3:30	2023-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.

[v-yiruan-msft]

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