Forum Discussion

SDream7's avatar
SDream7
Helper I
8 years ago

Merging queries based on multiple conditions

Hello, I'm having difficultly merging two tables through the merge query function. Here's my situation: Table A has numerous fields, but the ones of interest are [First Name], [Last Name], [C...
Need Help
RahulYadav's avatar
RahulYadav
Resolver II
8 years ago

Hi SDream7,

Please try following below steps for achieving this using dax.

 

1. The join fields should have different names in TableA & TableB.

2. Import both tables to Power BI.

3. Do not add joins on the tables.

4. Add a New Table using below DAX Formula.

   

TableC = DISTINCT( union(
    FILTER(CROSSJOIN(TableA,TableB),TableA[Client ID]=TableB[ClientID]),
    FILTER(CROSSJOIN(TableA,TableB),TableA[Personal ID]=TableB[PersonalID]),
    FILTER(CROSSJOIN(TableA,TableB),TableA[Concatenated First/DOB]=TableB[ConcatenatedFirst/DOB]),
    NATURALLEFTOUTERJOIN(TableA,TableB),
    NATURALLEFTOUTERJOIN(TableB,TableA)))

 

This will give you the details you are looking for. Sample Belw:

TableA:

 

TableB:

 

TableC:

 

Thanks,

Rahul

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    RahulYadav

    As per your point#3, you have said not to add any joins between tables.

    However, I get below error -

     

     

     

     

     

     

     

     

     

    Any idea?

    • RahulYadav's avatar
      RahulYadav
      Resolver II
      8 years ago

      Hi SDream7,

      Sorry for the confusion.

      I have added join based on ClientID field.

       

      Thanks,

      Rahul

      • SDream7's avatar
        SDream7
        Helper I
        8 years ago

        I got the same issue as the user above:

         

        "No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column."

         

        How would I fix this?  What do you mean you added join based on Client ID?

         

        Sorry, i'm new to BI :(