Latest Enterprise DNA Initiatives

Conditional, Unrelated Slicer

Hello,
Is it possible to change slicer options based on an unrelated slicer selection?

In the attached sample, currency and country are related. I am trying to have the currency selection also affected by the StateorProvince slicer. So for example, if all countries are selected, but only Alberta (Canada) StateorProvince is selected, can only local currency be shown and USD currency not shown on the currency slicer?

image

Dynamic Slicer Test v2.pbix (83.9 KB)

@DeanJ,

In order to get the slicers to behave the way you want them to you need to:

Merge the States or Provinces & Currency Selection Tables.

Then the table would look something like this:

Then the slicers will work the way you want them to.

Thanks
Jarrett

1 Like

Hello @DeanJ,

Thank You for posting your query onto the Forum.

As it was suggested in the previous post provided below that un-related slicers will not have any direct effect over the selection made between the two. You’ll be required to include that within the one table so that one slicer have the effect over another. You can go through the previous post for the entire explanation.

Extending to that part, I’ve added the Index column in the Sales Data and have added new table (in this case to provide the solution) which contains the columns of Index, States, Country and Currency. Now, just establish a one-to-many relationship between the tables based on the Index key or column and one slicer will be affected by the another. Simple!!! No complex techniques performed to achieve this.

Here’s the screenshot of the result provided for the reference -

I’m also attaching the working of the PBIX file for the reference. Again I recommend you to please go through the “Data Transformations and Modelling” course.

Thanks and Warm Regards,
Harsh

Dynamic Slicer Test v2 - Harsh.pbix (88.3 KB)

Country, State and Currency Selection.xlsx (11.4 KB)

2 Likes

Thanks @Harsh and @JarrettM for confirming that there is no way to do this without related slicers.

If possible, I was looking for a way for Country and StateorProvince slicers not to change if currency slicer was changed.

Also, one of my other requirements is to be able to select both US and Canada (Country slicer or a combination of StateorProvince slicer options) and show results as USD.

Hello @DeanJ,

For the 1st point, yes that’s possible. Select the “Currency” slicer and go to the Edit Interactions options and disable the slicers that are getting affected by the “Currency” slicer. Below is the screenshot provided for the reference -

Results

For the 2nd point, no that’s not possible. How’ll you show US and Canada both? While USD itself is mapped against US and not against Canada. For that please create your model accordingly if you want that to happen. And go through the modelling course to design and set-up the model.

Hoping this helps you. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Dynamic Slicer Test v2 - Harsh.pbix (88.3 KB)

Hi @DeanJ, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Thanks @Harsh. We decided to remove the currency slicer and just use a switch measure to show CAD if only Canada is selected and show USD for everything else.

Hello @DeanJ,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to assist you on this query.

Thanks and Warm Regards,
Harsh