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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
WimBi
Frequent Visitor

Calculate hours worked with category rate from other table

Hi!


I have a question about DAX. I’m trying to calculate the costs of the total hours worked in one table. The problem is the rate category is in a other table (see example data). I’m using DirectQuery mode, so calculated columns are a bit harder to use.


So far I got this far, but I need the “0001” to be variable, because different people work for different ‘categories’.

 

 

 

CALCULATE(SUM(urenverantwoording[Hours Worked]),urenverantwoording[Category]="0001") * CALCULATE(SUM(m_urensoorten[Rate]),urenverantwoording[Category]="0001")

 

 

Urenverantwoording = table 1
m_urensoorten = table 2

Employee (1)Hours worked (1)Category (1) id (2)Category (2) Rate (2)
JaBe0,51 412 null 
ErAa11 61 €    45,00
JaBe0,251 1081 €           -  
ErAa0,511 1291 €           -  
JaBe4,0112 1511 €    25,00
mont60,512 16u1 €           -  
ErAa0,2512 17x33 €           -  
ErAa0,2512 1813 €    40,00
ErAa0,2512 1991 €           -  
JaBe0,2511    
JaBe0,251    
JaBe0,251    
ErAa0,251    
ErAa0,2512    
JaMo091    

Can someone please help me? If I need to provide more information, please let me know!


Thanks in advance – Willem

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

Set up a one-to-many relationship from table 2 to table 1, then you can use

Total value =
SUMX (
    Urenverantwoording,
    Urenverantwoording[Hours worked] * RELATED ( m_urensoorten[Rate] )
)

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

Set up a one-to-many relationship from table 2 to table 1, then you can use

Total value =
SUMX (
    Urenverantwoording,
    Urenverantwoording[Hours worked] * RELATED ( m_urensoorten[Rate] )
)
WimBi
Frequent Visitor

Thank you! I also already found a other way, but this also works great!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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