Show duplicate values for a slicer

[rb161604888]

Hello,

1. Current State:

I currently have 2 tables as shown below.
Table 1:

 

Table 2:

Realtionship between the 2 tables:

Here is the current Slicer I have:

2. Future State:

Now, i want to add one more item to table 1 (line item 4) as shown below:

As soon as I add this item i get the following error:

How can i fix this? My final slicer should have the ability such that when I select either "London" or "Porto" in the slicer, it should show the corresponding value "Catergory A" and "200000" in the table in both the cases.

I will really appreciate it if you guys can help me with this issue.

Thanks in advance.

[v-yetao1-msft · Accepted solution]

Hi @rb161604888 

You have created a one to one relationship between two tables , so when you add duplicate value in one table , you will receive an error .

There is a more straightforward way, delete the relationship then add the value in table ,and then recreate a one to many relationship ，set the Cross filter direction is Both .

The final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regard

Community Support Team _ Ailsa Tao

[amitchandak]

@rb161604888 , If one of the table can have category Unique, You can make the relation 1-M from the unique side to non unique.

Or create a new category table and join with both tables

Category = distinct(union(distinct(Table1[Category ]),distinct(Table2[Category ])))

and now use category from category table in visual

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

[rb161604888]

Thanks @amitchandak , 

I was able to create a new table as per your instructions:

But, my slicer is based on the "City" column in "Table 1". How do i make my slicer work?

The visual currently shown below has "City" column in "Table 1" as slicer. The table visual on the right hand side of the snapshot below uses the "Category" i created using the formula you provided  and the "Value" column from "Table 2". This does not work currently, please help me with the next steps.

Thanks in advance.

[amitchandak]

@rb161604888 , Not ideal, But create a many to many join in this case

[rb161604888]

@amitchandak 
Still doesnt work. Is there any way for me to send the PBI to you?

Or does the solution only work when "Category" is used as a slicer instead of "City"?