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
alexa_0028
Resolver II
Resolver II

Poor performance of measure in composite model

Hi All,

I have the relationship like below and I am creating 2 measures which are giving me right results.
But the performance of my second measure is extremely slow. Can someone please help?

Data Model:

So I have a central model (tables highlighted in RED , connected in star schema and I have connected an external table to the central model like below). My "M Sales" table is also connected to Calendar table in model. I forgot to add that relationship to diagram.

alexa_0028_0-1648678619621.png



Measure 1: It calculates sales based on start and end date from external table.Every P. Code has a unique start and end date and this measure works fine.

var start_date = MIN('Freshness Flags'[Start Date])
var end_date= MAX('Freshness Flags'[End Date] )
RETURN
CALCULATE([Sales],KEEPFILTERS('Calendar'),
'Calendar'[Date]>= start_date && 'Calendar'[Date]<= end_date ,
'Product'[P. Code] in VALUES ('Freshness Flags'[Item_Code]) )

Measure 2: Performance issue
Basically I am recalculating this to fix the grand total
NIS w/ Kit NPI (Card) =
IF (HASONEVALUE('Product'[P. Code]),
[Measure 1],
CALCULATE(SUMX('External Table',[Measure 1])))



Also, if I make the visual like below , I get correct values from [Measure 2] but very bad performance.
alexa_0028_1-1648679051817.png

Can someone please help me to understand how can I improve [measure 2]?
It will be very helpful.

thanks in advance !!

1 ACCEPTED SOLUTION
alexa_0028
Resolver II
Resolver II

Hi All,

I got the performance improved by following below steps.
1. Check the meausre and saw which filter was being less performant.
2. It was due to filtering on date , as the date was being calculated over range start and range end
3. I added a column date in external table for all the combination of date between that range and connected it to the calendar table as well on date.

Finally my initial measures worked perfectly after this change and the performance was improved.
I think the issue here was calculating min/max for dates in the external table.

View solution in original post

7 REPLIES 7
alexa_0028
Resolver II
Resolver II

Hi All,

I got the performance improved by following below steps.
1. Check the meausre and saw which filter was being less performant.
2. It was due to filtering on date , as the date was being calculated over range start and range end
3. I added a column date in external table for all the combination of date between that range and connected it to the calendar table as well on date.

Finally my initial measures worked perfectly after this change and the performance was improved.
I think the issue here was calculating min/max for dates in the external table.

v-yalanwu-msft
Community Support
Community Support

Hi, @alexa_0028 ;

Can you share a simple file after removing sensitive information? I'm sorry I can't reproduce so I can't do a good test.


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

v-yalanwu-msft
Community Support
Community Support

Hi, @alexa_0028 ;

First you could try modify the measure1, change KEEPFILTER() to ALLSELECTED();By using ALLSELECTED, the cardinality of the iteration could be smaller if there are existing filters outside of the visual.

https://community.powerbi.com/t5/Desktop/ALLSELECTED-Vs-KEEPFILTERS-Which-is-better-in-terms-of/td-p...

 

Secondly, The fact that SUMX is an iterator function means that it iterates through every row of a specified table to complete the evaluation and then works out an additional piece of logic within the function.

But if you want to sum the measure, you might want to use sumx, or you might want to do it with some other column. If you use Sumx, you can try it

Measure 2 = SUMX( SUMMARIZE('Table',[Column1],"1",[Measure1]),[1])


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

Thank you for your suggestion @v-yalanwu-msft 
Still facing same issue , not loading well.

I have done something like below , to load measure 1 again in measure 2 . But that doesn't give me the right value for measure 2, though it works fast. It is because the external table is connected to product table.But I am trying to see visual for "C Account" table and one account can have multiple products attached. Thus the min/max of the start_date and end_date from measure 1 gets aggregated and is not  correct for every row combination of account and product in this approach.

any suggestions ??

amitchandak
Super User
Super User

@alexa_0028 , measure one like

Measure 1 =
var start_date = MIN('Freshness Flags'[Start Date])
var end_date= MAX('Freshness Flags'[End Date] )
RETURN
CALCULATE([Sales],KEEPFILTERS(
'Calendar'[Date]>= start_date && 'Calendar'[Date]<= end_date), Filter('Product',
'Product'[P. Code] in VALUES ('Freshness Flags'[Item_Code]) ) )

 

or

 

Measure 1 =
var start_date = MIN('Freshness Flags'[Start Date])
var end_date= MAX('Freshness Flags'[End Date] )
RETURN
CALCULATE([Sales],KEEPFILTERS(
'Calendar'[Date]>= start_date && 'Calendar'[Date]<= end_date &&
'Product'[P. Code] in VALUES ('Freshness Flags'[Item_Code]) )

 

 

measure 2 like
NIS w/ Kit NPI (Card) =
CALCULATE(SUMX(values('Product'[P. Code]),[Measure 1]))

Thanks for suggestion @amitchandak 
I am still having very bad performance after trying the above measures. 
I tried like below :

Measure 1 =
var start_date = MIN('Freshness Flags'[Start Date])
var end_date= MAX('Freshness Flags'[End Date] )
RETURN
CALCULATE([Sales],KEEPFILTERS(
'Calendar'[Date]>= start_date && 'Calendar'[Date]<= end_date), Filter('Product',
'Product'[P. Code] in VALUES ('Freshness Flags'[Item_Code]) ) )

 

measure 2 like
NIS w/ Kit NPI (Card) =
CALCULATE(SUMX(values('Product'[P. Code]),[Measure 1]))

Measure 1 became extremely slow after this change compared to before. While Measure 2 continued being bad.

I think the issue with my measure 2 is the sumx over the measure value.


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.

Top Solution Authors