How to left join / merge two tables but returning only the first matching record

[freidav1984]

Hi BI folks!

I am facing the following issue, could anybody help me?

I have two calculated table variables. Let's say:

Var VarTableA =

SELECTCOLUMNS(TableA,

"ID", TableA[ID],

"A", TableA[A],

"B", TableA[B]

)

Var VarTableB =

SELECTCOLUMNS(TableB,

"ID", TableB[ID],

"C", TableB[C],

"D", TableB[D]

)

I would like to merge VarTableB to VarTableA, but because, in VarTableB each ID record is duplicated, I would like to join each of the matching first ID records from VarTableB. 

Unfortunately, from technical issues, it is not an option for me to use power query in order to eliminate duplicates from VarTableB.

I would like to get this result by using DAX. Any thoughts on this, any help is really appreciated!

Thx!

David

[parry2k]

@freidav1984 you can also create a calculated table using following DAX expression:

Table Result = 
SELECTCOLUMNS ( 
    NATURALINNERJOIN ( 
        DISTINCT ( TableB ),
        TableA
    ),
    "ID", TableA[ID],
    "A", TableA[A],
    "B", TableA[B],
    "C", TableB[C],
    "D", TableB[D]
)

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[truptis · Accepted solution]

Hi @freidav1984 ,
Please follow the below steps:
1- create a flag column. 

You can first create a calculate column in the normal table to mark whether there is a entry in your table, and then create a measure to get the first value or "NA".

Flag = IF('VarTableB'[ID] IN VALUES(VarProductB[ID]), 1, 0)

Result =
VAR _count =
CALCULATE (
COUNT ( ('VarTableB'[ID] ),
ALLEXCEPT ( 'VarTableB', ('VarTableB'[ID] , 'VarTableB'[C], 'VarTableB'[D] ),
'VarTableB'[Flag] = 1
)
VAR _firstValue =
CALCULATE (
FIRSTNONBLANK ( 'VarTableB'[ID] , 'VarTableB'[ID] ),
ALLEXCEPT ( 'VarTableB', 'VarTableB'[ID] , 'VarTableB'[C], 'VarTableB'[D]),
'VarTableB'[Flag] = 1
)
RETURN
IF ( ISBLANK ( _count ), "NA", _firstValue )

Later you can use this table to do your mapping with VarTableA

@freidav1984 -> Please hit the thumbs up if it helps you. Thanks.