How to get away from Bookmark to create relationship on visuals?

[JustinDoh1]

I have my PowerBI file uploaded here.

I have three visuals (used Parameters) that are tied to Bookmarks.

(Currently, all these three Parameters are in "All" Bookmark.)

When I first created these PowerBI visuals, because these particual visuals are Parameters, I developed using Bookmarks.

Now, I came across to the point that I cannot continue doing this way (using Bookmarks).

I have to use "Location" filter to display these Parameters. 

Is it possible?

Didn't PowerBI come up with a new solution to get away from Bookmark like 3 years ago?

(I also tried creating relationship between three Parameter visuals with table for "Location", but as I expected, it would not work.) 

[hnguy71]

Hi @JustinDoh1 

Looks like you're trying to simply your process and get rid of bookmarks. Assuming you only want to see forecast for one location at a time, you can achieve this with a disconnected table and just one parameter field.

I'm just speculating here but this may be your expected results:

Step 01: Create a disconnected table to store your locations

Step 02: Replace the old slicer the new "Location" field 

Step 03: Re-purpose one of your parametere tables, doesn't matter which since they all have the same start and end. For my example, I'll use ARB.

Step 04: Adjust your Forecast measure with this instead:

Forecast = 
VAR _Base = [SUM C1-C3]

-- User Configurations
VAR _GetLocation = SELECTEDVALUE(Location[Location], MAX(Location[Location]))
VAR _ForecastVal = [ARB Value]
VAR _Provider = SELECTEDVALUE(tblScore[Provider Name])


VAR _Param =

SWITCH(
    TRUE(),
    _Provider = _GetLocation, _Base + DIVIDE(_ForecastVal, 2),
    _Base
)

RETURN

_Param

And with that, hopefully this is what you're looking for:

[JustinDoh1]

@hnguy71 

Thank you so much.

I think your new measure looks amazing.

I need to go back to my work on Monday, and ask my manager if having one parameter is acceptable.

I think knowing that bookmark would limit how filter works, one parameter should be only way to go.

Other than using this measure, there is no other way to show more than one parameter, correct?

[hnguy71]

Hi @JustinDoh1 ,

You're welcome! 

I'm confident your manager would find the solution acceptable. We can always modify it to meet expectations. 

[JustinDoh1]

@hnguy71 

Before I present this case of modified version of PBIX file to my manager, I have three questions for you:

Please find my latest version of PBIX file here.

I noticed that, If I have more than 1 Location selected, what is logic that gets applied for "WhatIf" scenario/paramter?

Or, should this "WhatIf" scenario/parameter be used for only one location?

It appear that when there are more than one location, change would only applied to the very bottom one.

I am curious what this part mean:

Thanks.

[hnguy71]

Hi @JustinDoh1 

Question 01:

VAR _GetLocation = SELECTEDVALUE(Location[Location], MAX(Location[Location]))

When you have more than one location selected, the optional argument from the function SELECTEDVALUE allows you to dictate what value to use. For this scenario, we decided that we're only going to allow value adjustment to one locatio.  If no location is selected or more than one is selected, then we'll use the last or max location found within the table. Of course, this can be changed based on the business needs.

Question 02: 

The "What-If" parameter can be used for all your locations, all at once, or only for selected locations. The parameter only captures the value, and doesn't limit or restrict any measure / calculation from using it. 

Question 03:
We're using a SWITCH function to decide which location we'll apply the forecast value to. From your original pbix, it seems you only wanted to apply to one location at a time using the formula:

_Base + ( _Param / 2 )

I adjusted the division calculation to use the DIVIDE function instead. Essentially, take the "What-If" value and divide it by 2 to match your earlier calculation logic. Same with question 01 and 02, we can selectively decide to apply the calculation to one, many, or all locations.

[JustinDoh1]

@hnguy71 

Thank you so much for your explanations.

I asked my manager, and she asked me to explore more for an ideal solution..  😞

Is there anyway to somehow, show the changes on all sites whatever (is/are) selected?

This is link to the latest PBIX copy that I was using.
Thank you so much!!

[hnguy71 · Accepted solution]

Hi @JustinDoh1 

hehehe, "ideal solution" is very subjective to the business needs, but yes... if you want to have the parameter apply to all selected locations, you can adjust your forecast measure as such:

Forecast = 
VAR _Base = [SUM C1-C3]

-- User Configurations
// Collects user selected location(s)
VAR _GetLocation = VALUES(Location[Location]) 

// Collects the What-If parameter value
VAR _ForecastVal = [WhatIf Value]

// Collects the table's current row level context
VAR _Provider = SELECTEDVALUE(tblScore[Location])                                       

// If current location in context is selected, add forecast, else return base value
VAR _Output = IF(_Provider IN _GetLocation, _Base + DIVIDE(_ForecastVal, 2), _Base)

RETURN _Output

And this would be your expected output:

[JustinDoh1]

@hnguy71  I would have to test more with real data later (possibly tomorrow), but I would have to definitely give you "congrats" and "thank you!".

Update:

I might have to post a separate thread as I got some different requirement 😞 from my manager (maybe I misunderstood expectation from yesterday's email) as she wants separate "whatIf" buttons to be shown for different locations. (Like having individual "WhatIfs" for different locations and shown and hidden depending on selecting location). I will let you know the thread info. after I post. 

[lbendlin]

What is the actual business problem you are trying to solve? Do your report users care about this, or are they looking to get insights from the data?

[JustinDoh1]

@lbendlin 

Thank you for asking.

Sorry, but, I am not exactly sure what you are asking.

My manager wanted me to come up with a solution to modify the "what if scenario" (parameter), but filtering out by one location (due to security / visibility).

[lbendlin]

Use RLS for that.

[JustinDoh1]

@lbendlin 
Our team (at work) is already applying RLS for other visuals (like Matrix), but the problem/hardest part is how to manage "parameter".

I only found a way using Bookmark initially.  Is there any other way?  @hnguy71 came up with a new measure which would use one parameter, and I am going to ask my manager if that is acceptable at this point.