joining tables

[mejiaks]

Hi guys

I have two tables

Kms and Incidents

in both tables i have the fileds

Country, Division, Month, Year

The only difference is the last field

Kms.[Total Kms]

Incidents.[Total Incidents]

what I need is to have a third table with common fields and [Total Kms] From Kms Table and [Total Incidents] From Incidents table. 

something like this

Kms Table

Country, Division, Month, Year, Total Kms

GT          CLS            1        2018    100

GT          CLI             1        2018     200

HN         CLS            1        2018      10

Incidents Table

Country, Division, Month, Year, Total Kms

GT          CLS            1        2018    1

GT          CLI             1        2018    2

HN         CLS            1        2018    0

and have this result

Kms And Incidents

Country, Division, Month, Year, Total Kms   Total Incidents

GT          CLS            1        2018    100                 1 

GT          CLI             1        2018     200                2 

HN         CLS            1        2018      10                  0

is this possible?

TIA

[v-yulgu-msft · Accepted solution]

Hi @mejiaks,

You could create a calculated table with below DAX formula:

Join Table =
ADDCOLUMNS (
    Kms,
    "Total Incidents", LOOKUPVALUE (
        Incidents[Total Kms],
        Incidents[Country], Kms[Country],
        Incidents[Division], Kms[Division],
        Incidents[Month], Kms[Month],
        Incidents[Year], Kms[Year]
    )
)

Best regards,

Yuliana Gu

[mejiaks]

That is EXACTLY what I needed and EXACTLY what I was looking for

simple, straight forward and fast

Thanks a lot

[affan]

Hi @mejiaks,

As you have shown the tables in your post, there is no unique Key field based on which we can join the tables. This means that we need to provide a field on which PBI can identify that which value from Incidents value should be matched with which row in the Kms table.

To resolve this we need to create this field. I have made the below video which will help you to resolve this issue.

  

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan