Slicer on conditionnal displaying measure based on 2 columns from 2 tables

[Anonymous]

Hello all,

I have created a measure that displays a specified value based on the value of two columns in two different tables:

Table 1: Business Segment

Business Segment	Application
Garden	DIY
House	Kitchen

Table 2: Products

ProductCategory	ProductName
Tool	Spoon
...	...

I also have my data table linking all the information:

Table 3: datas

ProductName	Application	Sales
Spoon	DIY	1
Spoon	Kitchen	2

This is of course an example, I have about 400 products, as many applications etc.

Here is the result I would like to obtain:

Table 4: Results

Business Segment	ProductName	Display
Garden	Spoon	Specialties
House	Spoon	Tool

For this, I used this measure which works well:

Display = IF (
CONTAINS ( Products, Products [ProductName], "Spoon" ),
SWITCH (
TRUE, 
CONTAINS ('Business Segment', 'Business Segment' [Business Segment], 'House'), SELECTEDVALUE(Products [Product Category]),
CONTAINS ('Business Segment', 'Business Segment' [Business Segment], 'Garden'), 'Specialities'),
)

So I end up with a measure that displays the values I want correctly, but my problem comes when I want to create a "slicer" on this measure. Because at the end I would like to be able to study my sales according to what is displayed in the [Display] column and see my sales for "Specialities", "Tool" or other.

If you also have another idea/ know another way of doing this measure to have a dynamic display I am a taker!

Thank you in advance !

[Anonymous]

@AllisonKennedy So yes, I could make a table with the rules that we tell PBI and this table would be like :

ProductName	Business Segment	Display
Spoon	House	'Specialties'
Spoon	Garden	corresponding [Product Category] = Tool

Because all depends of the Business Segment in fact.

All this is because we are selling many products and when we want to analyse our sales we need to be able to analyse by Business Segment and within those Business Segments we do not analyse our products the same way. In the Business Segment "House" we decided to analyse our sales by group of products that are chosen for a technical reasons and I can make a table of this corresponding group of products. For the Business Segment "Garden" we analyse our sales with the ProductCategory that is already a column in my pbix file.

My goal is to have the same file for the study of all Business Segment otherwise it's too complicated, this is why I need to have a display that is dynamic depending on the Business Segment I want to focus.

Hope this is clear enough...

[AllisonKennedy]

@Anonymous  As you have said, this is just sample data, so are you wanting this to work for more than just 'spoon' ? I am not sure what you're trying to display with 'display'? It looks like you are wanting to replace the [Category] value with 'Specialties' for some cases, but I'm not clear yet what that case may be in general?

[Anonymous]

Hi @AllisonKennedy ,

Thanks for the reply.

So, yes I want this to work for more than "Spoon".

What I want to display with "Display" is just a new calling of the Product that depends on the ProductName AND on the Business Segment that are both columns located in different tables.

This is in fact because the Spoons (as many other products) are sold within several Business Segment and I want to be able to study the sales depending on "categories" within the Business Segments.

For example in the Business Segment "Garden" I want to name myself the category, this is why I wrote "Specialties" but in other Business Segment such as "House" I want to display the "Product Category"

Hope my explanation makes it clearer.

Thanks a lot in advance!

[AllisonKennedy]

@Anonymous  You must have a pattern or set of rules that we can tell Power BI, so you either need to build another table that lists all the categories and products and what you want to rename the category, or you need to define the rules that determine when you rename and when you don't. Are there set rules we can use? Please try to explain how you decide when to rename the category.