help with slicer and relationship

[Jvn1]

I am new to power BI, I have 2 tables and which doesnt have unique columns on both the tables. I am using direct query to Power BI. 

Example as below:

Product	Map
Red 001	white 020
Red 002	white 010
white 010	white 010
white 020	white 020
Blue 100	white 020
Blue 101	white 010
Yellow 111	white 020

Product	Description
Red 001	R00011
Red 001	R00012
Red 002	R00021
Red 002	R00022
white 010	W00101
white 010	W00102
white 020	W00201
white 020	W00202
Blue 100	B01001
Blue 100	B01002
Blue 101	B02001
Blue 101	B02002
Yellow 111	Y10001
Yellow 111	Y10002

I want to create 1 slicer,

• When select 1 product -> Red 001, i get the below result

Product	Red 001	white 020
Description	R00011	W00201
Description	R00012	W00202

• When select 1 product -> white 020, i get the below result

Product	white 020	Red 001	Yellow 111
Description	W00201	R00011	Y10001
Description	W00202	R00012	Y10002

Plz help with the solution. Also the Power BI - Version: 2.65.5313.1562 64-bit (January 2019), i don't get the option of cardinality - Many to Many - Pic below

cardinality

[v-lili6-msft]

HI, @Jvn1 

• When select 1 product -> white 020, i get the below result

Product	white 020	Red 001	Yellow 111
Description	W00201	R00011	Y10001
Description	W00202	R00012	Y10002

What is the logic of this result? I think If there should be "blue 100" in this result?

you should update your power bi desktop and Many to Many will be available.

Best Regards,

Lin

[Jvn1]

@v-lili6-msft 

oh yea, i just noticed it - Thank you correcting.

• When select 1 product -> white 020, i get the below result

Product	white 020	Red 001	Blue 100	Yellow 111
Description	W00201	R00011	B01001	Y10001
Description	W00202	R00012	B01002	Y10002

I will update the Power BI Desktop and check.

[v-lili6-msft]

hi, @Jvn1 

For this case, Do you mean that when select one product, then it will return all the product that have the same map value?

In that case, When select 1 product -> white 020, you get the below result white 020, Red 001, Blue 100, Yellow 111

Product	Map
Red 001	white 020
Red 002	white 010
white 010	white 010
white 020	white 020
Blue 100	white 020
Blue 101	white 010
Yellow 111	white 020

But why select 1 product -> Red 001, It only returns Red 001 and white 020?

Product	Map
Red 001	white 020
Red 002	white 010
white 010	white 010
white 020	white 020
Blue 100	white 020
Blue 101	white 010
Yellow 111	white 020

why does it not return white 020, Red 001, Blue 100, Yellow 111?

What is your logic of it?

Best Regards,

Lin

[Jvn1]

@v-lili6-msft 

Yes. I am trying achive that as i am comparing all the products to base product(white's).

In visual window, I want to view only the immediate compare of the product selected (Red 001 -> white 020). But whenever a base product is selected, i want to see the all the models compared.

Is there any way to achieve this view?

[luisamboni]

you can achieve this by merging the tables in query editor

[Jvn1]

@luisamboni 

I merged the tables, when i use slicer as map column - i get the results(description) of products from both columns.

• When select 1 product -> white 020, i get the below result

Product	white 020	Red 001	Blue 100	Yellow 111
Description	W00201	R00011	B01001	Y10001
Description	W00202	R00012	B01002	Y10002

I unable to get this result

• When select 1 product -> Red 001, i get the below result

Product	Red 001	white 020
Description	R00011	W00201
Description	R00012	W00202