select the opposite of the chosen in a slicers

[Anonymous]

Good morning. I have two boards.

Table Products.

Lot	Product	Amount
1	grape juice	10
2	apple juice	23
3	apple juice	43

Ingredients Table

Lot	Product	Amount
1	grape juice	5
1	grape juice	5
1	Water	2
1	Sugar	1
1	Water	4
1	nectar de uva	3
2	apple juice	10
2	apple juice	13
2	Water	10
2	Sugar	3
2	Water	5
2	apple nectar	5
3	apple juice	22
3	apple juice	21
3	Water	13
3	Sugar	5
3	Water	20
3	apple nectar	8

These two tables are related by the batch field. I'd like to see in a pie visualization the decomposition of a product. I choose this product from a slicer with the product field in the products table. The problem I have is that in the ingredients table also comes the product in addition to the ingredients in which it is broken down. I wondered if you can think of any filtering in the pie visualization that all products (in the ingredients table) appear except the one that matches the name of the product selected in the slicer with the product field (from the products table). Thanks for the help.

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

It’s my pleasure to answer for you.

According to your description,I think you can create a measure to calculate the desired result.

Like this:

Measure = IF(SELECTEDVALUE(Ingredients[Product])in DISTINCT(Products[Product]),0,1)

 

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

A few ways to do this.  One would be to do it in a measure, but a simple way might be to make a column on your second table with this formula, and then use it as a visual or page filter in the Filter Panel.

Include = IF(Ingredients[Product] = RELATED(Products[Product]), "N", "Y")

This assumes you have a 1:Many between the tables.

Regards,

Pat

[mahoneypat]

Please confirm you are putting a DAX calculated column (not a measure) on the Ingredients table with the formula, and that you have a 1:Many relationship between the Products and Ingredients table. If so, the RELATED should pull the product value for that batch to the ingredients table for the comparison to the product value on that row of the ingredients table.

Regards,

Pat

[Anonymous]

Thank you for your answer @mahoneypat I am I have put the column you mention but I get error in putting:

Nueva_Columna-IF(Ingredients[Product] - RELATED(Products[Product]), "N", "Y")

the Products[Product] part tells me: The 'Products[Product]' column does not exist or has no relation to the tables available in the current context.

I tried to put only the column without the name but it tells me: The RELATED function expects a full column reference as an argument.