Merging 3 Tables into one with related values

[thmonte]

I'd like to merge these 3 tables:

date	taskID	a.Hours	personID
6/5/2018	AAAA	4	1
6/5/2018	AAAA	4	1
6/5/2018	BBBB	4	2
6/5/2018	BBBB	4	3
6/6/2018	CCCC	8	4
6/6/2018	DDDD	8	5
6/6/2018	EEEEE	8	6

date	personID	b.Hours
6/5/2018	1	4
6/5/2018	1	4
6/5/2018	2	8
6/5/2018	3	8
6/6/2018	4	8
6/6/2018	5	8
6/6/2018	6	8

taskID	c.Hours
AAAA	4
AAAA	4
BBBB	4
BBBB	4
CCCC	8
DDDD	8
EEEEE	8

Resulting Table

date	personID	a.Hours	b.Hours	c.Hours
6/5/2018	1	8	8	8
6/5/2018	2	4	8	8
6/5/2018	3	4	8	8
6/6/2018	4	8	8	8
6/6/2018	5	8	8	8
6/6/2018	6	8	8	8

In addition to results table, when I click on a row, I'd like a seperate visual to display all associated task IDs for the date selected.  I'm sure some relationship tables need to be setup but I don't know what else to do here.  Its a tought one for me.  Any help would be greatly appreciated.

[MFelix · Accepted solution]

Hi @thmonte,

You need to setup 3 different dimension table with unique values in order to make then related with other tables. I'm using dax to make this tables but this can also be made in Query editor:

Tasks = DISTINCT(UNION(ALL(Table1[taskID]);ALL(Table3[taskID])) )

Persons = DISTINCT(UNION(ALL(Table1[personID]);ALL(Table2[personID])))

DimDate = CALENDAR("01/01/2018";"31/12/2018") 

To have a more complete calendar table follow this link.

Then you need to setup the relationships between all the tables:

DimDate[Date] -> Table1[Date] (one to many)

DimDate[Date] -> Table2[Date] (one to many)

Persons[personID] -> Table1[personID] (one to many)

Persons[personID] -> Table2[personID] (one to many)

Tasks[TaskID] -> Table1[taskID] (one to many) (cross filtering both sides)

Tasks[TaskID] -> Table3[taskID] (one to many)

This will get the expected result be aware that you should use in your final table the values from the Dimension tables (Date, Persons, Tasks) to make your visuals and then the hours from the other tables.

Be aware that in the image above the slicer is acting with the taskID table the other one is not since you have fully detail of the personID you won't be abble to show the activity for that day for all persons. In your data one person does one task per day however if in your live data you have more than one task if you select the lina in the table it will give you all the activities for that person on that day.

See attach PBIX file.

Again the way the table are calculated is one of the many ways you can do it in PBI, depending on the actual data you can have better and faster ways of doing this.

Regards,

MFelix

[nirvana_moksh]

Load all the queries individually and then in the Power Query mode just do 'Append Queries as New' this will basically be the union of all. I think you will able to do the rest following that, as a good practice I always disable the load of the queries powering this Append in order to reduce load time.

[MFelix]

Hi @nirvana_moksh,

Sorry for replying to this but when you do append of this tables the result will not be the expected one this will give you a single table with all the lines one after the other and the columns that aren't matching will be filled with nulls.

In this case you could use a merge queries that allows to make the relationship between several tables but again in this type of data this would not give the expected result because there are rows that when merged with other tables would create double or triple rows, so in the end the expected result would not be achieved.

Regards,

MFelix

[nirvana_moksh]

I scrolled further down to the post and realzied I skipped the last image of the 'Resulting Table' . Thanks for the reply Felix and I agree with your solution.

[thmonte]

This worked great @MFelix Thank you for your help.  One more question.  Is it possible ot create a filter/measure to compare all 3 of the hours even tho they are not in the same data table?