Duplicate table visual rows.

[StuartSmith]

I have 2 data sets\tables with a relationship (Many to Many with Both as cross directional) of "Country".  I then create a table visual and all columns are from the same table (Carrier Table) and it displays perfect.  

I then add the column "No. of SIM's" and "Invoice Date" from table 2 and to reduce the number of rows, configure a "Top n" filter on "Invoice Date" as the "Latest".

This then displays the below table, but it shows duplicate rows, but only the highlighted rows are valid.

Why is it showing these extra rows.

Thanks in advance.

[amitchandak]

@StuartSmith , is you data model showing Many to Many join. Because these values are doubling up.

I this is true, refer. Not sure you can do this in your data model

https://www.seerinteractive.com/blog/join-many-many-power-bi/

[StuartSmith]

Thinking about it, the main table will only ever have 1 of each country, with no duplicates. The 2nd table will have multiple of the same country, but the auto relationship creation is doing "Many to Many", so i guess this is why I am seeing extra rows.  But if I try to change the relationship to 1:*, it says the cardinality isnt valid for this relationship. Why?

[StuartSmith]

I created the below...

TotalRows=COUNTROWS('table1')

DistinctRows= DISTINCTCOUNT('table1'[country])

and the table that I thought had unique countries, in fact has 3 x US rows, so cant do 1 to many and therefore can only to "Many to Many", which is causing the below issue where its doubling up rows.  I really need to have the relationship as "Country" without the below issue.  Is there a workaround.

[v-xicai]

Hi @StuartSmith ,

Since the relationship between the two tables is Many to Many, which is to say there are multiple instances of one table to match multiple instances of another table. In your scenario, for each combined row value [Region] , [Country] and  [Carrier] , there are two values "421" and "19684" , so the returned result has duplicated row even though make filter data.

Best Regards,

Amy 

Community Support Team _ Amy

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

[StuartSmith · Accepted solution]

I managed to find a solution over the weekend to prevent the "Many to Many" relationship by creating a new uniqiue column in each table using...

Key = 'Carrier Matrix'[Region] & " - " & 'Carrier Matrix'[Country]& " - " & 'Carrier Matrix'[Carrier]

and this created a 1 to many relationship and seems to work.  It even helped me identify columns on either table that had mis-matching values, such as typo's as they couldnt create a relationship between tables.  Once the typo's had been fixed or row content matched, duplicate columns vanished.

Thanks for reply.

[vanessafvg]

you are obviously missing a piece of the relationship that it needs to filter the correct row, are you able to share your data?

[StuartSmith]

Unfortunately, cant share data.  I pursumed that "Country" to "Country" would work as a relationship. What could I be missing?

[StuartSmith]

I thought the "Country" column would be the ideal relationship link between the 2 tables, but have removed that relationship and tried a couple of differnt column relationships and the relationship between "Vendors" seems to work.  Not to sure why this over country works and will have to test to make sure the data I want displayed is being displayed.

[vanessafvg]

country can be a good relationship but it wont make your line unique, what is the granularity of your model? The granularity being the level your data is sitting at. At the moment you joining on country - When you say they are duplicated why do you say they are duplicated?