Forum Discussion
Duplicate table visual rows.
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.
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.
9 Replies
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/
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?
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.
- you are obviously missing a piece of the relationship that it needs to filter the correct row, are you able to share your data?
Unfortunately, cant share data. I pursumed that "Country" to "Country" would work as a relationship. What could I be missing?
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.
