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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.