Forum Discussion

Digger's avatar
Digger
Icon for Post Patron rankPost Patron
3 years ago
Solved

Convert Many to one relation to one to one

I need compare two tables by text columns

Table1[MatchCol] has only unique text

Table2[MatchCol] has dublicates

 

I need found all MatchCol Ids from Table1 and Table2, to know which Ids from Table1[MatchCol] matches Table2[MatchCol] and wise versa (find difference in Tables)

 

one to one relations do this tricks but only with both tables unique values, and i got error "The cardinality you selected isnt valid for this relationship

How to do that? 

Need Help
Show and Tell
Tips and Tricks
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Here is one way without changing the relationship:

     

    Matching = 
COUNTROWS(
    INTERSECT(VALUES('Table 1'[T1]), VALUES('Table 2'[T2])))
    No Match in T2 =
SUMX (
    VALUES ( 'Table 1'[T1] ),
    CALCULATE (
        IF (
            ISBLANK ( MAX ( 'Table 1'[T1] ) ),
            BLANK (),
            COUNTROWS ( EXCEPT ( VALUES ( 'Table 1'[T1] ), VALUES ( 'Table 2'[T2] ) ) )
        )
    )
)

    No Match in T1 = 
COUNTROWS(EXCEPT(VALUES('Table 2'[T2]), VALUES('Table 1'[T1])))

     

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Here is one way without changing the relationship:

     

    Matching = 
COUNTROWS(
    INTERSECT(VALUES('Table 1'[T1]), VALUES('Table 2'[T2])))
    No Match in T2 =
SUMX (
    VALUES ( 'Table 1'[T1] ),
    CALCULATE (
        IF (
            ISBLANK ( MAX ( 'Table 1'[T1] ) ),
            BLANK (),
            COUNTROWS ( EXCEPT ( VALUES ( 'Table 1'[T1] ), VALUES ( 'Table 2'[T2] ) ) )
        )
    )
)

    No Match in T1 = 
COUNTROWS(EXCEPT(VALUES('Table 2'[T2]), VALUES('Table 1'[T1])))