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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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