Forum Discussion
How to achieve such table
- 3 years ago
Here is one way.
First add a new column to each table to establish the "Type":
Add a new column to the outside hours table for "project":
Create dimension tables for both employee and type following this pattern:
Employee table = DISTINCT( UNION( VALUES('On Project Table'[Emp ID]), VALUES('On Project Table'[Emp ID])))Create a dimension table for project using:
Project Table = ADDCOLUMNS( DISTINCT( UNION( VALUES('On Project Table'[ProjectDsc]), VALUES('Out of hours Table'[Project]))), "Project", IF([ProjectDsc] = "No Project", BLANK(), [ProjectDsc]))Set up the model as follows
Create a measure for the hours:
Sum Hours = SUM('On Project Table'[Hours on Project]) + SUM('Out of hours Table'[Hours outside project])Set up a table visual using the fields from the dimension tables and the measure to get:
Sample PBIX file attached
Here is one way.
First add a new column to each table to establish the "Type":
Add a new column to the outside hours table for "project":
Create dimension tables for both employee and type following this pattern:
Employee table =
DISTINCT(
UNION(
VALUES('On Project Table'[Emp ID]), VALUES('On Project Table'[Emp ID])))
Create a dimension table for project using:
Project Table =
ADDCOLUMNS(
DISTINCT(
UNION(
VALUES('On Project Table'[ProjectDsc]), VALUES('Out of hours Table'[Project]))),
"Project", IF([ProjectDsc] = "No Project", BLANK(), [ProjectDsc]))
Set up the model as follows
Create a measure for the hours:
Sum Hours =
SUM('On Project Table'[Hours on Project]) + SUM('Out of hours Table'[Hours outside project])
Set up a table visual using the fields from the dimension tables and the measure to get:
Sample PBIX file attached
Thanks
Can i make a matrix out of it, to look like that
rows:
first layer be manager (assume its field in first table)
on project / outside
employee (assume its field in first table)
hours
?
- PaulDBrown3 years ago
Community Champion
Sure! To cater for the added field for manager, change the Employe table to the following:
Employee table = SUMMARIZE('On Project Table', 'On Project Table'[Emp ID], 'On Project Table'[Manager])Sample PBIX file attached