cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Remove Duplicates NATURALINNERJOIN

Hi,

 

When I join 2 tables, I am receiving duplicate values, because in same cases for the same entry I have 2 values. I would like to know, at the end of joining, if I can make distinct, and get only one.

 

 

 

 

Join = 
VAR A =
    SELECTCOLUMNS (
        TableA,
        "An", RELATED(Advertisers[An]),
        "Key", FORMAT(TableA[Key],"string")
    )
VAR B =
    SELECTCOLUMNS (
        TableB,
        "idAn", TableB[IdAnunciante],
        "NameAn", TableB[NomeAnunciante],
        "Key", FORMAT(TableB[KeyMkt],"string")
    )
VAR Result =
    NATURALINNERJOIN( B, A )
VAR Dist = SUMMARIZE(Result, [IdAn] , [NameAn], [An] )
RETURN
    Dist

 

 

 

 

Result (In same cases I get 2 entrys):

 

IdAnNameAnAn
1153XPTOTesteXPTO
1153XPTO

XPTO

 

What I want (I just want one entry, regardless of which entry is):

 

IdAnNameAnAn
1153XPTO

XPTO

 

or

 

IdAnNameAnAn
1153XPTOTesteXPTO

 

I need to do this with DAX, because tableA as more than 100M entrys

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @flaviocarvalho ,

 

Has your problem been solved?

If not, do you mind sharing your .pbix file? Or show me the sample data of TableA, TableB and  Advertisers.

Or you can use table visual to get the table.

v-lionel-msft_0-1603790849698.png

Or create another table.

'Sheet2' table is your 'join' table.

v-lionel-msft_1-1603790889092.png

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Community Support
Community Support

Hi @flaviocarvalho ,

 

Has your problem been solved?

If not, do you mind sharing your .pbix file? Or show me the sample data of TableA, TableB and  Advertisers.

Or you can use table visual to get the table.

v-lionel-msft_0-1603790849698.png

Or create another table.

'Sheet2' table is your 'join' table.

v-lionel-msft_1-1603790889092.png

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Creating another table solved the problem. The problem was that I need a Many-to-One connection to another table.

Community Support
Community Support

Hi @flaviocarvalho ,

Try to use MAXX() function.

Join = 
VAR A =
    SELECTCOLUMNS (
        TableA,
        "An", RELATED(Advertisers[An]),
        "Key", FORMAT(TableA[Key],"string")
    )
VAR B =
    SELECTCOLUMNS (
        TableB,
        "idAn", TableB[IdAnunciante],
        "NameAn", TableB[NomeAnunciante],
        "Key", FORMAT(TableB[KeyMkt],"string")
    )
VAR Result =
    NATURALINNERJOIN( B, A )
VAR Dist = SUMMARIZE(Result, [IdAn] , [NameAn], "An", MAXX(Result, [An]) )
RETURN
    Dist

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Hi @v-lionel-msft ,

 

I think we are close. My original table without MAX is:

 

IdAnNameAnAn
1153XPTOTestXPTO
1153XPTOXPTO
1164YeeYee
2154WhiskeyWhiskey

 

Now the result is the following:

IdAnNameAnAn
1153XPTOWhiskey
1164YeeWhiskey
2154WhiskeyWhiskey


But I need the Max / Min "An" of each ID, like this:
( I tried Maxx(values(Result[IdAn)) but cannot find the table )

 

IdAnNameAnAn
1153XPTOXPTO
1164YeeYee
2154WhiskeyWhiskey





Super User IV
Super User IV

@flaviocarvalho , See if this can help

 

At the end of your code

 

VAR Dist = SUMMARIZE(Result, [IdAn] , [NameAn],"An" , max([An]) ) // or use min
RETURN
    Dist

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi @amitchandak , I tried to put a Max or even a filter with count, but I always have the error "Cannot find name [An])". I think it had to be something like Max(TableName[An]), but it also doesn't works, because doesn't accept Max(Result[An]) 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors