How to achieve such table

[Pbiuserr]

Hello

I have table with project hours and table with hours outside the project. I'd like to produce a final table like in the capture. I've put only columns mandatory for that, there are plenty in both (not equal amount, but I am interested mainly in those for that visual). Is it something I can achieve via DAX/PQ?

Thank you in advance for your help

[PaulDBrown · Accepted solution]

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 

[Pbiuserr]

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 

?

[PaulDBrown]

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