Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsLoad 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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI 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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis 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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |