Forum Discussion

mejiaks's avatar
mejiaks
Frequent Visitor
7 years ago
Solved

joining tables

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

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

3 Replies

  • affan's avatar
    affan
    Solution Sage
    7 years ago

    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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    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's avatar
      mejiaks
      Frequent Visitor
      7 years ago

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

       

      simple, straight forward and fast

       

      Thanks a lot