Help with Selective Application of What-If Parameters

First of all, this is a continuation of a topic that was discussed not long ago. This is the original discussion in case you want additional background/context:

In addition, I have included a quick video (with audio) explaining what I had done before and what I’m needing help with right now. The video is at the bottom of this post. The explanation in the video is a little rushed, but I had to keep it short so my file wouldn’t be too big to upload. Sorry!

Also attached below is a sample PBIX file that takes what I had shared in the previous discussion and starts to add “What-If Analysis” capability for report users. The parameter allows the user to increase/decrease the number of “Type 1” events (which won’t make sense until you look at the file) by 0-100%. As the parameter value changes, the visuals adjust based on the new value for “Type 1” events at all locations.

Where I’m getting stuck is in trying to make it possible for the user to select one or more locations, and having the adjustment made by the parameter value only apply to the selected location(s). Locations can be “selected” in one of two ways:

  • selecting one or more locations from the table that lists all locations, or
  • using the region slicer to select all locations in that region.

I know none of this really makes sense unless you examine the PBIX file first, so if there is additional info that I can provide to help clarify what I’m trying to accomplish, please let me know, and I’ll try to provide it.

Thanks in advance!

Explanatory video:


Demo file: eDNA Demo 3.pbix (1.2 MB)
1 Like

@DaveC,

Great to hear from you again - hope you’re doing well in the midst of all this craziness.

First off, kudos for the clear explanation of your problem, the provision of the PBIX file, and for being the first person on the forum to my knowledge to submit a video further explaining the issue. Can’t ask for more than that…

This is a fascinating question. I won’t have time to dive into this in detail until after work today, but here’s my initial thinking about how to attack it.

What’s needed in general terms is:

IF( location selected = FALSE, value, value * whatif parameter )

Pretty simple in concept, but the trick will be determining which locations are selected and which are not.

To develop a measure to do this, we’ll need to understand the full filter context applied to each location. The best tool I’ve found for that is the automated DUMPFILTERS function in DAX Studio, which you can then drop into a tooltip to show all the filter conditions currently in play:

From the understanding of the full filter context, I think we can develop the condition statement for the IF() above.

Here’s the article on the Dump Filters process if you want to have a go at this in the interim:

Hope this is helpful. I’ll be glad to work with you on this later tonight, if you and/or others on the forum haven’t cracked it already by then.

  • Brian
1 Like

@DaveC,

Just thought of a much simpler way to do this - use some combination of IN, CONTAINSROW and/or CONTAINS (the first two are basically synonymous) to determine if a given location falls within the ALLSELECTED table or not. Once we determine this, implement the general strategy laid out above.

See what you think, but I believe this approach is pretty promising.

  • Brian

As always, thanks for your help, @BrianJ! It will probably be a day or two before I can really dedicate some time to diving into this, but I will let you know how it goes.

By the way, you mentioned before that you might use my previous example as a case study in some training. I’m curious as to whether you were able to use it and how the training went (or is going).

@DaveC,

I’ll try to dive into the nitty-gritty of the DAX on this as well over the next few days ( my planned forum time tonight got consumed by the online grocery scavenger hunt…)

I’m definitely still planning to use your example as a training case study. Just had to set it aside temporarily with two big projects going to production in April. Hope to get back to it later this month, and will be sure to shoot you a copy when it’s done.

  • Brian

Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. If you have a follow question or concern related to this topic, feel free to re-open the thread by removing the removing the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum Thanks!