Forum Discussion

jolyp's avatar
jolyp
New Member
4 years ago
Solved

Merging data from different tables with several commons fields

Hello

 

I'm new in "measure" and "DAX" and after digging into this forum I did not find a solution yet.

 

I have two tables for employees booking

1/ for project booking

"employee name" "date of booking" "project ID" "number of hours"

 

2/ for indirect (non productive) booking

"employee name" "date of booking" "indirect ID" "number of hours"

 

I would like to have a table (or a measure ?) in order to be able to report per employee the both project & indirect bookings.

 

What is the easiest way ? add a columm (possible from 2 different tables ?) , create a measure (syntax DAX ?) ?

 

Thanks for your support in this new steps into power bi (I'm now quite confortable with the first one : reports from one table 😉 )

 

Regards

Need Help
  • jolyp's avatar
    jolyp
    4 years ago

    Thanks Ashish

     

    After adding a column "type" and making the title of columns identical for both tables , I just used the "add queries"  to add the indirect table to the project table and I works fine. I can have all booking hours or filter using the new "type" column

     

    It was simpler that I expected

     

    Thanks for support

5 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi jolyp 

     

    Use RELATED DAX function to add columns to your tables, for example if you want to add Indirect Date of Booking to the Project Booking table, use this code to add a new column (I assume there is a relationship between those 2 columns):

    Indirect Date Of booking = related('indirect booking'[date of booking])

    then add other columns:

    Indirect ID = related('indirect booking'[indirect ID])
    Indirect No. Hours = related('indirect booking'[number of hours])
     
     
    Or use this code to add a new table:
    Table = ADDCOLUMNS('project booking',"Indirect Date of booking",RELATED('indirect booking'[date of booking]),"Indirect ID",related('indirect booking'[indirect ID]),"Indirect No. Hours",RELATED('indirect booking'[number of hours]))
     
     

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


    Appreciate your Kudos!!


     
     
    • jolyp's avatar
      jolyp
      New Member
      4 years ago

      Thanks for your suggestion

       

       

      but in fact I wanted to merge the data not to add new column, so using add query 'indirect table added to project table" it works fine and simple

       

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Here's my suggestion.  Edit the ID heading on both tables to a standard one, say ID.  Create another column in both tables titled as Type with only 2 entries under that column - Project and Indirect.  Append the two tables.  Create a Calendar Table with a relationship (Many to One and Single) from the Date of booking column of the appended table to the Date column of the Calendar Table.  In the Calendar Table, create all grouping columns such as Year, Month name and Month number (sort the Month name by the Month number).  To your visual/slicer/filter, drag any date dimension from the Calendar Tables.  Write this measure

    Hours = sum(Data[Number of hours])

    Hope this helps.

    • jolyp's avatar
      jolyp
      New Member
      4 years ago

      Thanks Ashish

       

      After adding a column "type" and making the title of columns identical for both tables , I just used the "add queries"  to add the indirect table to the project table and I works fine. I can have all booking hours or filter using the new "type" column

       

      It was simpler that I expected

       

      Thanks for support