Dynamically Adjusting Slicer Values Based on Other Measures

Power BI
1

Thanks Brian, just learning from bits and pieces hope to be as good as you guys when im older lol…

There is one other thing with it that is bugging me if you want to have a play with it? The values of the SAP minute slicer the upper and lower range limit id like to automate as the values of X-3 and X+3

Almost complete PBIX is attached

image
image.png1305×353 59.5 KB

Regards

DanPower BI Initiatives2.pbix (982.9 KB)

2

@Krays23,

Thanks for posting the updated PBIX. I’ve got some ideas on this - I’ll take a run at it later today and get back to you soon.

  • Brian
3

@Krays23,

I haven’t forgotten about this one. I’ve been hammering away at it the past couple of days, but the GENERATESERIES function turns out to be pretty quirky and doesn’t behave like a normal table function in some contexts. I’ve thrown a big bag of tricks at it so far, including ALLSELECTED, TREATAS, VALUES/IN, edit interactions, and sync slicers and still can’t get it to filter correctly. One more completely different approach to try tomorrow…

  • Brian
4

@Krays23,

After four days of hammering away at this, including a mind-numbing journey deep into the heart of darkness of ALLSELECTED, I can absolutely see why this has been bugging you. Here are all the things I’ve found that make me 95% certain it is not possible at all (and 100% certain it is not possible for me…):

  • A slicer requires a column reference, and this column reference would need to be a calculated column in order to filter to the selected values of the generated series in the Normal Distribution table
  • However, there can be problems associated with changing filter context in calculated columns, and probably the worst case scenario for that per the article referenced above is changing filter context via iterating on ALLSELECTED (which I think would be necessary for the calculated column here)
  • The Single Value slicer option you are using in the lower left-hand visual I think (based on Googling and my own experimentation) can only be accessed via an unaltered what-if parameter or GENERATESERIES function. As soon as I tried to apply the ALLSELECTED filter to either of those, the Single Value choice disappeared.
  • Finally, even IF we were able to overcome all of the above hurdles, the slicer values still would not be fully dynamic, since in a calculated column the ALLSELECTED filter context is determined only when the data is initially loaded, and not again until the data is reloaded/refreshed.

Sorry – I gave this my best shot, and learned a lot in the process, but in the end I’ve got nothing for you…

  • Brian

P.S. If future versions of PBI allow the use of measures in slicers, then you’re good to go since I was able to write a measure that successfully generated the filtered series.

5

Hi Brian,

Awesome mate thanks for trying it sure is a frustrating one because all the info it needs is right there in front of you. Seems strange something that looks so simple is in fact not so simple.

I appreciate your efforts though and helping me to the point i am at now, its pretty good as it is and can be managed.

Thanks

Dan

6

@Krays23,

I agree completely – this one is frustrating since it feels like we should be able to get closer than we’ve been able to. The calculated column issue (bullet point #1 above) means it will never be fully dynamic, but I got inspired by @sam.mckay’s updated video on TREATAS to give this one final shot before admitting defeat.

I created two measures (max and min) using TREATAS to transfer the filter from the normal distribution generated series to the SAP Minutes generated series. Here’s the max series measure (the min measure is identically structured):

SAP Minutes Treatas Max = 

VAR MaxTreat =
CALCULATE(
    MAXX(
        ALLSELECTED( 'SAP Minutes Slider'[SAP Minutes] ),
        'SAP Minutes Slider'[SAP Minutes]
    ),
    TREATAS(
        VALUES( 'Normal Distribution Chart'[Generated Series] ),
        'SAP Minutes Slider'[SAP Minutes])
)

RETURN
MaxTreat

From the screenshot below, both measures seem to be working exactly right - returning the proper values in the red cards.

I then generated a parameter column via:

SAP Minutes Treatas = GENERATESERIES( [SAP Minutes Treatas Min], [SAP Minutes Treatas Max], 1)

and then dropped this column into the orange slicer. However, not having the full data set available, I can’t do a complete refresh to see if the orange slicer endpoints will update properly when the full report recalculates on refresh. Thus, can you please take the file below, make sure the data path is correct, run a full refresh and let me know if the orange slicer endpoints change to the values in the red cards?

image
image1018×606 123 KB

Thanks! Really curious to see if this works…

7

Hi Brian,

HAHA your addicted huh i know the feeling. Jeez you must be soooooo close with this as the treat as in the red boxes works !

OK so i fully refreshed the PBIX i didn’t change any data paths it seemed to refresh OK. However the values in the orange slicer didn’t change. why would you need to do the refresh?

Capture
Capture.JPG1170×838 52.6 KB

What are these values ? -1295 and 1750 is that some how still getting them from the complete data source and not the filtered data table?

We want the values that we have for the SAP Data Measures X-1σ and the X+1σ remember were only manually filling in the Normal deviation curve X axis range when we want it to automatically take the X-1σ and the X+1σ values is that the missing part from your formula? as shown below in screen shot.

Capture2
Capture2.JPG1139×681 121 KB

I have attached the full excel data set for you if it helps you called SAP Last 40 Ship Sets (Hassan) .

Data A350-900 for Paint (Hassan).xlsx (1.8 MB)

8

@Krays23,

I haven’t forgotten about this one - just been a bit swamped at work coming back after the holidays. I have continued to chip away at it though, and think I’m making some progress. Plan to really dig it again and try to crack it over the weekend.

One thing I need though - can you please also post the “Complete Parts.xlsx”? Need that to do the full refresh of the calculated columns.

Thanks.

  • Brian
9

Hey Brian

No worries been busy too.

Here is the complete parts file much appreciated hang in there.

Dan

Complete Parts.xlsx (3.8 MB)

10

Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.

11

Hi @Krays23, a similar topic was covered in this forum thread link. You might get additional tips here