In the Slicer, expose some values to the user, but execute the filtering by another value

[AlexGurovich]

Hi:

I have a unique and only Slicer in my Power BI Desktop. I want the Slicer to expose some values to the user, but "behind the scenes", I want that Slicer to execute the filtering of the page by the values of another column.

For Example, let's imagine I have the following Table, with Columns A and B:

Column A	Column B
China	150
France	20
Argentina	310

I would like the Slicer to expose in the user interface the values of Column A ("China, France, Argentina")

But if the user chooses, for example, "France", then the execution of the filter "behind the scenes" should be done by the value "20" (Column B)

Or if the user chooses "Argentina", then the execution of the filter is by the value "310" (Column B)

Appreciate,

[parry2k · Accepted solution]

@AlexGurovich here is the updated pbix file using relationship, it is bit tricky but works.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[AlexGurovich]

@parry2k you are a genius, although it didnt worked for me in my real model I dont know for what reason!

I'm anyway mark it as "Accept as Solution" cause it meets the problematic as stated in this post.

[parry2k]

@AlexGurovich solution is attached, tweak it as you see fit.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[AlexGurovich]

@parry2k  we are almost there, you are a genius!!!! I'm going to reccomend you everywhere...

There's one last thing...That I should have said and didn't, and that is, that the 2 tables MUST be realted (for reasons different than the ones stated in this post). Related like in the following screenshot:

When that relation ship exists, then the Slicer doesn't work according to my original problem statement, as you can see in the following screenshot. Help help help! Can something be done without the need of removing the relationship between the two tables????????????????

Sincerely,

[parry2k]

@AlexGurovich two questions come in the mind:

- what happens if no value is selected in the slicer?

- When do you say filter by another column? What you are tyring to filter? What is your expectations?

[AlexGurovich]

Hi @parry2k 

I’ll try to clarify with a different example. Thanks for coping with me.

Imagine I have the following Table ProductNames:

id	PopularNameOfTheProduct	OfficialNameOfTheProduct
1	A	W
2	B	W
3	C	W
4	D	X
5	E	Y
6	F	Z
7	G	Z

And then imagine I have the following Table PricePerProductPerDate :

id	PopularNameOfTheProduct	OfficialNameOfTheProduct	Date	Price
1	A	W	01/Jan	$100
2	A	W	02/Jan	$120
3	B	W	02/Jan	$110
4	B	W	03/Jan	$150
5	B	W	10/Jan	$140
6	D	X	15/Mar	$130
7	F	Z	25/Feb	$100

What I would like to achieve is a Slicer that exposes the options A, B, C, D, E, F, G. In other words, a Slicer that exposes the options in Column “PopularNameOfTheProduct” of Table ProdcutNames . That's the 1st thing.

And then the 2nd thing, is that below the Slicer there must be simple visualization that exposes the Table PricePerProductPerDay . But here comes the tricky part: if the user filters the Slicer by “A”, then the visualization should expose rows 1, 2 3, 4 and 5 of Table PricePerProductPerDay, that are the rows that have "W" as OfficialNameOfTheProduct ("W" being the OfficialNameOfTheProduct corresponding to option "A" selected in the Slicer). In other words, expose in the visualization those 5 rows, as opposed to exposing only rows 1 and 2, that are the ones that correspond to "A". 

Regarding your question "what happens if no value is selected in the slicer?", my answer: whatever is simpler  and easier in order to achieve the 2 goals stated above. But ideally, if no value is selected, expose in the visualization the full table PricePerProductPerDay without any filter.

Hope I made myself clear.