JOIN 2 tables with DISTINCT(UNION)) result many 2 many relationship

[admin_xlsior]

Hi,

Recently I was joining 2 tables, so that I can get the unique value of this tables.

So I create this query :

Unique Location = DISTINCT(

    UNION(

        DISTINCT('Table A'[Location]),

        DISTINCT(Table B[Location])       

    )

)

FYI, the column [Location] is just a location code with many duplicates since it is a transaction table. And the reason I want to create a new unique table because I want to put a relationship between this two but cannot since it will create Many to Many relationship.

So in that query, logically, it would create a new table with unique value of "Location", right ?

So does what it looks like in the table result.

But strange is when I want to connect this new table "Unique Location" to Table A or Table B, it still tell me it is Many 2 Many.

What is wrong with that query ?

Thanks,

[parry2k]

@admin_xlsior can you check what output you are getting Unique Location table. I just tested at my end and it worked as expected.

[admin_xlsior]

Yes, 

it does have unique value :

Location

010
109
110
111
112
113
114
115
116

[parry2k · Accepted solution]

@admin_xlsior hmm seems like there is a blank row and wondering if that is the root cause.

[admin_xlsior]

 Actually I expect that, but if it is the blank causing it, I can remove it.

However been trying this query :

DISTINCT(

    UNION(

        DISTINCT(ALLNOBLANKROW('Table A'[Location])),

DISTINCT(ALLNOBLANKROW('Table B'[Location]))

    )

but cannot get rid of the blank row.

Also tried the FILTER like this :

DISTINCT(

UNION(

        DISTINCT(FILTER(VALUES('Table A'[Location]), LEN('Table A'[Location])>0)),

        DISTINCT(ALLNOBLANKROW('Table B'[Location]))   

    )

the first table filter (Table A) already gave an error saying "a circular dependency was detected", but  I think this is because the way I get this data.

Anyway, I thought DISTINCT will ignore Blank ? VALUES include Blank, right ?

Thanks,

[admin_xlsior]

You are right it does because of the blank row. And I need to remove it by this query :

DISTINCT(FILTER(VALUES('Table A'[Location]), 'Table A'[Location] <> "") ),

Strange, with the same logic, only I'm using Len(Table A[Location] > 0), it does not work. 

Many thanks for your help,

[parry2k]

@admin_xlsior sorry I was away, seems like you have solution in place. Glad to help!!