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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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