Dynamically Adjusting Slicer Values Based on Other Measures

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


DanPower BI Initiatives2.pbix (982.9 KB)


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


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


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.

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.




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 =
        ALLSELECTED( 'SAP Minutes Slider'[SAP Minutes] ),
        'SAP Minutes Slider'[SAP Minutes]
        VALUES( 'Normal Distribution Chart'[Generated Series] ),
        'SAP Minutes Slider'[SAP Minutes])


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?

Thanks! Really curious to see if this works…

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?

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.

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)


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.


  • Brian

Hey Brian

No worries been busy too.

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


Complete Parts.xlsx (3.8 MB)

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.

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