I hope a DAX ninja warrior can suggest a thing or two for me to try. Because I have run out of ideas.
Please see the attached images for the challenge.
I have 12 what-if parameters, one for each month of the year, and I am able to “flex” /incorporate these with values from other tables in the model.
At the total level, no matter what I try, the total does not show any values.
For comparison I have included the results from the original column of values that I am trying to flex.
My approach at the total level has been to try to do what has been demonstrated on Enterprise DNA by Sam, by using virtual tables, but the virtual table seems to be empty when it gets to the total level.
I cannot share the actual .pbix file owing to the nature of the company information contained. I do hope the image of the DAX code and the output provides enough context.
Can someone kindly explain what I am missing and suggest some way(s) I can “encourage” the selected values within the SWITCH statement to stay/be present within the virtual table?
Would be very much appreciated.
Your switch statement is set up without a catch-all condition at the end to provide a value if none of the above statements are true. Thus, if no month is selected, _SwitchLogic will return no value. (Note: I think it’s always good practice to include that final catch-all statement, even if the value is just “ERROR” or some obvious nonsense value like -999999)
But then your final RETURN statement, if no month value is present (e.g., the total line), calls _TotalLevel, which in turn calls the SWITCH statement, which can’t handle a condition which no month is selected. That’s why it’s returning blanks in the total. If you replace this statement I have marked with a measure that provides the proper total values in context without calling the switch statement, it should work.
Hej NajahS
Thanks for the response. And the link.
Using the approach suggested in the link it looks like I would have to create an expression to capture the results from each month and then add it together at the end. This is what I initially did.
But I have a few of these what-if parameters within the model, flexing other columns and it seems that creating these big total expressions for all of them made the DAX calculations very very very slow, especially once loaded to Power BI service.
I was trying to find a way to make the code more efficient. Hence the attempt to use SUMMARIZE at the total level. Can you suggest why the SUMMARIZE statement isn’t working?
Thanks for the response.
I appreciate the input can’t be specific without the .pbix and will be just feeding you “scraps”
I think you are right.
The issue is that the capture all statement I created initially seems to have made the DAX code run very slow. (I have a few of these 12 what-if parameters flexing other columns. I regret doing that now.).
It involved a SUMX for every month, with it’s respective what-if result, added together.
I am trying to make the code more efficient by trying to harvest the results already created within the earlier SWITCH statement.
I guess there might be no easy-ish way to do that. If you can think of how I can try that please let me know.
OK, now I’m really out on a limb writing measures without seeing the data, but I’ll keep sawing…
I think what you need is a simple total measure that will calculate properly within the context of your _TotalLevel virtual table (i.e., by month given how you’ve specified the evaluation context within _TotalLevel). Something like:
FcastMinusBud =
I can’t speak to the speed or efficiency of this without a PBIX to test on, but if this doesn’t get you what you need, perhaps you can randomize the values in your file and post a version that has no confidential information.
Unfortunately it is extremely difficult to figure out the problem and find a solution without the pbix file at this stage. I second @BrianJ that the problem might be with the _Switchlogic. I hope your problem does resolve quickly but unfortunately can’t really do much without the pbix file.
Thanks for posting your question @Mo.jo.jo. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.
Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Including all of the above will likely enable a quick solution to your question.
Hi @Mo.jo.jo, we’ve noticed that no response has been received from you since the 22nd of March. 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. 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 checkbox. Thanks!
Hello
Indeed this seems to have been indirectly resolved as at earlier today.
The indirect “fix” was to create the SWITCH expression in one measure and then refer to that measure/reference that original measure in a new measure containing the SUMMARIZE statement. Seems to allow the total row to populate figures within SUMMARIZE, which was the result I was after.
Thanks to @NajahS & @BrianJ for the suggestions to try and resolve this. Much appreciated to you both.
