I have an issue with my sales report. I have two slicers:
A KPI slicer that toggles between sales, units, GP and other relevant KPI
A Horizon slicer that toggles between current year (CY), PY, vs PY, etc.
I’ve created two measures as noted below such that when a user toggles the slicers it gives them the right output… Example: CY Sales, PY units, etc.
This is done using a switch statement and has drastically affected the performance of my report. If I use the direct measure MTD sales, it takes less than a second to show. However, the measures that helps to toggle between the slicers takes close to 6 seconds and the more scenarios I add in vs PY, vs PY%, etc. the longer it takes to load.
What can I do to change to improve the performance of my report
Thanks Antriksh. I had seen this video before and enabled best practices using variables and looking for CallBackSetIDs in my DAX query. But I’m not sure what else I can do. I think in SWITCH it computes all the variables before running the DAX logic and it seems that the more switch conditions I add the longer it takes.
Just trying to understand if there’s anything else that can be done. My model is a LiveConnect to the dataset where the measures are built in the dataset. Could it be because it’s Live Connect it’s slower?
Hi Avalon, in my experience, having worked with similar set up - eg LiveConnect to the dataset where measures are built, I also found that the report pefromance started lagging when building measures on top of the dataset. Wonder if you have an opportunity to build these measures directly into the dataset, rather than trying to build on top?
The main reason why the report is lagging in service: You may be creating too many datasets in addtion to the “original dataset”. Users are hitting various datasets, which then competes with other datasets. Depending on your service set up (either Pro or Premium workspaces), each additional dataset is competing for the capacity.
However, the service may not be an issue here at all - would be keen to hear what the dax gurus can suggest.
Thanks Adelin. Yes I noticed that creating measures outside the dataset resulted in issues. Hence I moved the measures to be built into the dataset and don’t have multiple datasets in my report. I echo your thoughts and getting a DAX guru perspective
I’m not quite a DAX guru, but I believe you should be utilizing Bookmarks to toggle between the views of the different measures rather than incorporating SWITCH or IF statements in your measures.
Hi @avalon.ds, did the response provided by the other users help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Hi @avalon.ds, we’ve noticed that no response has been received from you since the 24th of June. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @avalon.ds, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
