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.
Hello, I'm seeking advice on any ways to optimize the following DAX measures to enable the engine to perform the calculation without any visual / report level filter or slicer. On both desktop and publishing to the online service, I receive a resources exceeded error message when it tries to iterate through all 14M order line items YoY, in arriving at the card total without any filter. I believe it's necessary for this calculation to be peformed as a DAX measure, since the stakeholder wants to slice by varying dimensions & granularities impacting whether or not a given product is recurring YoY within the given context and then arrive at its variance allocation to price vs volume, accordingly. Below, I've put in bold font where I may not be following best practice. Appreciate any help!
Solved! Go to Solution.
I may have at least improved the measure with the following update below. The report card & table visuals run quicker without the error message on desktop with this revision, at least. Perhaps, still not ideal, not sure.
I may have at least improved the measure with the following update below. The report card & table visuals run quicker without the error message on desktop with this revision, at least. Perhaps, still not ideal, not sure.
Hi @BenBell ,
Glad you figure it out by yourself. Would you mind accept your reply as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!
Best Regards,
Community Support Team _ kalyj
Hi @BenBell ,
According to the formula, you havn't used too complicated function, but there're too many variables in the formula, it spends too much CPU in the calculation. I think this led to the error.
To prevent too heavy calculation in a measure, I suggest you try to break it down into several measures or calculated columns.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your response! I'm not sure I agree though that a measure branching approach would be more performant than the variables approach I've taken.
https://www.sqlbi.com/articles/variables-in-dax/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |