Forum Discussion

StuartSmith's avatar
StuartSmith
Power Participant
5 years ago
Solved

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 ...
Need Help
  • StuartSmith's avatar
    StuartSmith
    5 years ago

    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.
StuartSmith's avatar
StuartSmith
Power Participant
5 years ago

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's avatar
v-xicai
Community Support
5 years ago

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's avatar
    StuartSmith
    Power Participant
    5 years ago

    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.