Dynamic Slicer by unrelated measure- is it possible?

[DeanJ]

EDNA Community,
Once again I am asking for your support. I have a dataset which is populated with both USD and local (non-USD) currency for each location, regardless of if location is in the US or not.

The report has a disconnected slicer which allows a measure to switch between USD and local currency.

What I am trying to do is to remove the slicer option for local currency if only US locations are selected. I can create measures to see if only US or only Canada is selected, but not sure how to apply them to the currency filter.

Desired Behavior
US and Non US countries are selected- show both currency options: This works

Only US is selected- show only USD currency options- This is what I’m trying to achieve.

Here is a sample .pbix
Dynamic Slicer Test.pbix (76.4 KB)

[Harsh]

Hello @DeanJ,

Thank You for posting your query onto the Forum.

The solution contains two parts: First is optional, if you want to make the changes you can (It’s Recommended but again as mentioned, it’s optional). The second part is the solution part.

Firstly, there’s a problem with the data model as well as the relationship created between the “Sales” table and “Table” table.

The union which you’ve done in the “Table” table contains the “Blanks” so firstly please remove the blanks from that table. In my case, I’ve loaded the new file to provide the solution with the table name “States or Provinces” in the model by replacing the “Table” table.

Also the relationship which you had created was flowing from “Fact Table - Sales” to “Dimension Table - Table” which is incorrect. Below is the screenshot of the relationship status from your file provided for the reference -

Rather the relationship should flow from “Dimension Table - Table” to “Fact Table - Sales” in this case I’ve changed the Dimension Table to “States or Provinces” (Table which I’ve created). Below is the screenshot provided for the reference of the revised relationship status -

Now secondly, in “CurrencySelection” table I’ve added one more column with the name “Country” in it. And below is the revised formula provided for the reference alongwith the screenshot of the revised table -

CurrencySelection = { ( 1 , "USD" , "US" ) , ( 2 , "Local" , "Canada" ) }

Since relationship cannot be created between “States or Provinces” table and “CurrencySelection” because relationship between two dimension tables cannot be created and in this case, it’ll also provide an error of “Circular Dependency”. And relationship cannot flow from “Fact Table” to “Dimension Tables” and that’s why “Country” column has been added so that “Country” field from the “CurrencySelection” table can be dragged into the slicer to achieve the results.

Lastly, once the country column is added into the “CurrencySelection” table then I created a relationship between “CurrencySelection” table and “Sales” table as well based on the “Country” column. So now, the overall relationship status looks like this -

Once the data model problem is sorted (optional) and “Country” column is added in the “CurrencySelection” table, now just drag the “Currency” field and “Country” field from the “CurrencySelection” table to achieve the results. Below is the screenshot of the results provided for the reference -

I’m also attaching the working of the PBIX file for the reference. Also I recommend you to please go through the “Data Transformations and Modelling” course which is availble onto our education portal. Below is the link provided for the reference.

Hoping you’ll find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Dynamic Slicer Test - Harsh.pbix (83.3 KB)

Data Transformations & Modeling

[DeanJ]

Thanks @Harsh ! That is not my actual data model, just an example I put together quickly- thanks for your comments. I will give that solution a try.

[EnterpriseDNA]

Hi @DeanJ, did the response provided by @Harsh 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!

[EnterpriseDNA]

Hi @DeanJ, we’ve noticed that no response has been received from you since the 21st of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @DeanJ, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

[Harsh]

Hello @DeanJ,

Can you please create a new thread? Since this thread has already been solved 8 days ago?

Thanks and Warm Regards,
Harsh

[DeanJ]

Will do, thanks.