Forum Discussion

Pbiuserr's avatar
Pbiuserr
Post Prodigy
3 years ago
Solved

How to achieve such table

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

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    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 

     

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    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 

     

    • Pbiuserr's avatar
      Pbiuserr
      Post Prodigy
      3 years ago

      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's avatar
        PaulDBrown
        Community Champion
        3 years ago

        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