Merging data from different tables with several commons fields

[jolyp]

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

[Ashish_Mathur]

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 · Accepted solution]

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

[Ashish_Mathur]

You are welcome.

[VahidDM]

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]

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