Forum Discussion
Optimising the DAX formula
Hi All,
I am pretty new to the forum, and not sure if this question has been asked before..
I have written following query
Change in Stock = SUMX(FILTER('Model Simulation','Model Simulation'[SU]=MAX('Simulator-SU'[Plant Code])),'Model Simulation'[Manipulated PBI-Savings])
All the parameters here(including the result) happen to be measures, the reason being that I want dynamic columns(input values change using a what-if parameter) except 'Simulator-SU'[Plant Code]
I am trying excel equivalent of SUMIF in powerBI, the query delivers results, but takes ages to run (1-2 minutes) and my data only has around 2500 rows.
I am trying to get an output in a following manner
a 3000
b 5000
c 6000
where a,b,c happen to be 'Simulator-SU'[Plant Code] and the numbers happen to be 'Model Simulation'[Manipulated PBI-Savings]
And the input looks like following
a 300
b 4000
a 400
a 500
b 6000
c 5000
c 600
where a,b,c happen to be 'Model Simulation'[SU] and the numbers happen to be Model Simulation'[Manipulated PBI-Savings])
Is there anyway, that the efficiency of code could be improved.
Thanks
2 Replies
Hi Anonymous
It seems you may create relationships for the two tables instead of creating the measure. Attached the sample file. If it is not your case, could you share the sample file and explain more about your expected output for your scenario so that we could help further on it.You can upload it to OneDrive or Dropbox and post the link here.
Regards,
Cherie
- Thanks for replying to my post Cherie, have improved the performance by using var, but overall the formula still takes time...so my current formula is.. Change in Stock = var change = SUMX(FILTER('Model Simulation','Model Simulation'[SU]=MAX('Simulator-SU'[Plant Code])),'Model Simulation'[Manipulated PBI-Savings]) return change Will share the sample data Thanks
