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

    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'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.