Poor performance of measure in composite model

[alexa_0028]

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.

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.

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

thanks in advance !!

[alexa_0028 · Accepted solution]

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]

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]

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.

[alexa_0028]

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]

@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]))

[alexa_0028]

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.

[alexa_0028]

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