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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
BIanon
Resolver I
Resolver I

Measure performance issue

Hello community,

Here is some sample data:

 

idtripidValueValue2
112,15
212,17
312,13
123,72
223,76
323,78
131,14
231,12
331,15
431,18
140,79
240,74
340,732
150,94
250,965

 

I am trying to sum all of the values in the [Value] column but only once for every TripId. My current measure looks like this:

 

SUMX(VALUES('Table'[TripId]), CALCULATE(MAX('Table'[Value])))

 


I have been getting some performance issues with this measure unfortuately. I've tried doing some basic tests where I just put Year, Month and the measure in a visual

PeriodMSRows in table in selection
Single year6505347756
Two years148712138754
Three yearsVisual has exceeded the available ressources19318853



I get how iterating over the table is bad for performance vs a simple sum, but I have so few rows to work with (~33 m rows total) so it just feels like I must be missing something obvious since I am getting such bad performance from a simple measure.

Are there any tricks that I can use or a different DAX pattern perhaps? My only idea is adding aggregation tables, but it just seems excessive for something so basic and with so small data volumes

Note that my calendar dimension is filtering this fact table through a date key and has a one-to-many relationship.

If I do a simple EVALUATE on just the measure, grouped by nothing, the measure runs in 1 MS for all of my data in DAX Studios.

The following query in DAX Studios runs in 2900 MS:

 

EVALUATE

CALCULATETABLE(
	ADDCOLUMNS(
		VALUES( 'Calendar'[Year] ),
		"Value", [Measure]
		)
		)

 

 
2900 MS is obviously a very slow measure, but I am surprised that it cannot run at all, not even locally in desktop.

2 REPLIES 2
v-kaiyue-msft
Community Support
Community Support

Hi @BIanon ,

Based on the information you've provided, the performance bottleneck seems to be related to the way the metrics are calculated, try changing sumx to something like calculate(sum(), filter()) to see if that improves the speed. Here's a related document that will hopefully help you: DAX Performance issue: why is my SUMX() so slow? - Stack Overflow.

In addition, performance is also related to filters, filters, and data models, which can be found in the documentation:Optimization guide for Power BI - Power BI | Microsoft Learn.

 

Also, although you mentioned that adding an aggregate table seems a bit much, for measurements that aggregate large amounts of data, adding an aggregate table can dramatically improve performance, and it's not a bad way to go .

If your Current Period does not refer to this, please clarify in a follow-up reply.

 

Best Regards,

Clara Gong

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

amitchandak
Super User
Super User

@BIanon , The measures seems fine, but try this

 

SUMX(Summarize('Table','Table'[TripId], "_1",MAX('Table'[Value])), [_1])

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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