Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BobKoenen
Helper IV
Helper IV

Filtering hours table based on projectmanager in the projectstable...difficult ;)

HI all,

 

I am breaking my mind over this issue, I hope you can help me. My data looks like this

Data/fact Hourstable

EmployeeProjectcodeHoursdate
JaneA45-5-2020
JoeA35-5-2020
JaneB26-6-2020
BillC56-5-2020
JaneC38-7-2020
JoeB51-9-2020

 

I have 3 linked dimension tables: Employees, Projects and a Calendar. The projecttable is linked with the Hours via the ProjectCode

The project table has the following columns

ProjectcodeProjectManager
AJoe
BJoe
CJane

 

Now I want to create a measure which gives me the amount of hours the ProjectManager has spend on the project. The output I want to visualise in a table that should look like this

ProjectcodeProjectmanagerHoursAll Hours
A37
B57
C38

 

I cannot seem to Create the ProjectmanagerHours measure because I already linked the project table based on the Projeccode.

 

Can you help me?

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@BobKoenen,

 

Try these measures:

 

All Hours = SUM ( Hours[Hours] )

Project Manager Hours Calc = 
VAR vProjMgr =
    MAX ( Projects[Project Manager] )
VAR vResult =
    CALCULATE ( [All Hours], Hours[Employee] = vProjMgr )
RETURN
    vResult

Project Manager Hours = 
VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE ( Projects, Projects[Project Code] ),
        "tmpProjMgrHours", [Project Manager Hours Calc]
    )
VAR vResult =
    SUMX ( vTable, [tmpProjMgrHours] )
RETURN
    vResult

 

DataInsights_0-1606232969335.png

 

In the table visual, add Projects[Project Code] and the measures [Project Manager Hours] and [All Hours]. The measure [Project Manager Hours] (which is based on measure [Project Manager Hours Calc]) is necessary in order to calculate totals correctly.

 

DataInsights_1-1606232982318.png

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
DataInsights
Super User
Super User

@BobKoenen,

 

Try these measures:

 

All Hours = SUM ( Hours[Hours] )

Project Manager Hours Calc = 
VAR vProjMgr =
    MAX ( Projects[Project Manager] )
VAR vResult =
    CALCULATE ( [All Hours], Hours[Employee] = vProjMgr )
RETURN
    vResult

Project Manager Hours = 
VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE ( Projects, Projects[Project Code] ),
        "tmpProjMgrHours", [Project Manager Hours Calc]
    )
VAR vResult =
    SUMX ( vTable, [tmpProjMgrHours] )
RETURN
    vResult

 

DataInsights_0-1606232969335.png

 

In the table visual, add Projects[Project Code] and the measures [Project Manager Hours] and [All Hours]. The measure [Project Manager Hours] (which is based on measure [Project Manager Hours Calc]) is necessary in order to calculate totals correctly.

 

DataInsights_1-1606232982318.png

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@BobKoenen,

 

Here's a simpler solution. Measures below:

 

All Hours = SUM ( Hours[Hours] )

Project Manager Hours = 
SUMX ( Projects,
    VAR vProjMgr = Projects[Project Manager]
    RETURN
    CALCULATE ( [All Hours], Hours[Employee] = vProjMgr )
)

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.