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 am trying to show a visual that shows actual logged hours per project vs the planned hours. I have two tables, one is an export from the time logging software.
Project ID | Main Project | Work Package | Date | Employer | Hours |
1 | Project 1 | WP 1 | 01-01-2020 | emp1 | 2 |
2 | Project 1 | WP 2 | 01-01-2020 | emp1 | 4 |
2 | Project 1 | WP 2 | 01-01-2020 | emp2 | 5 |
1 | Project 1 | WP 1 | 02-01-2020 | emp1 | 4 |
3 | Project 2 | WP 1 | 02-01-2020 | emp2 | 3 |
and another table that functions as the projects database
Project ID | Main Project | Work Package | Planned Hours | Duration (weeks) |
1 | Project 1 | WP 1 | 25 | 12 |
2 | Project 1 | WP 2 | 30 | 12 |
3 | Project 2 | WP 1 | 40 | 9 |
4 | Project 2 | WP 2 | 30 | 9 |
Now for the visual I have selected the Hours and Work Package column from the first table. The user can select the main project from a selector. This works as expected and shows a bar graph with the hours per Work Package.
If I select the Planned Hours and Work Package from the other table it also does what expected, it shows a bar graph with the expected values from the project database table.
However, when I try to add the Planned Hours to the first visual, it just sums up all the hours by Main Project, instead of also grouping it by Work Package. I cannot figure out how to make this work. Does anyone have any recommendations?
Solved! Go to Solution.
Thank you for your reply!
I realised I actually already had a key column like that, the project ID, since they were unique. So I wasn't sure what you meant by joining them first but then decided to check the model view and noticed there was no relationship between the two tables.
Simply dragging between the project ID labels between the two tabels and setting the direction to both solved the problem and functionality is exactly as expected now!
Thanks so much for your input
If the second table is the master table
Then in both table create a new column
Key = [Main Project] & "-"& [Work Package]
And join them.
Else create a new dimension table with same key and join them
Dimension table
distinct(
union(
selectcolumn(Table1,"Project ID",Table1[Project ID],"Main Project",Table1[Main Project],"Work Package",Table1[Work Package],"key",Table1[Main Project] & "-"& Table1[Work Package]),
selectcolumn(Table2,"Project ID",Table2[Project ID],"Main Project",Table2[Main Project],"Work Package",Table2[Work Package],"key",Table2[Main Project] & "-"& Table2[Work Package])))
Thank you for your reply!
I realised I actually already had a key column like that, the project ID, since they were unique. So I wasn't sure what you meant by joining them first but then decided to check the model view and noticed there was no relationship between the two tables.
Simply dragging between the project ID labels between the two tabels and setting the direction to both solved the problem and functionality is exactly as expected now!
Thanks so much for your input
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 |
---|---|
111 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |