Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Can someone please help me to understand how can I improve [measure 2]?
It will be very helpful.
thanks in advance !!
Solved! Go to 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.
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.
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.
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.
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 ??
@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.
User | Count |
---|---|
93 | |
90 | |
90 | |
81 | |
49 |
User | Count |
---|---|
156 | |
145 | |
104 | |
72 | |
55 |