cancel
Showing results 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

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) JaBe 0,5 1 4 12 null ErAa 1 1 6 1 €    45,00 JaBe 0,25 1 10 81 €           - ErAa 0,5 11 12 91 €           - JaBe 4,01 12 15 11 €    25,00 mont6 0,5 12 16 u1 €           - ErAa 0,25 12 17 x33 €           - ErAa 0,25 12 18 13 €    40,00 ErAa 0,25 12 19 91 €           - JaBe 0,25 11 JaBe 0,25 1 JaBe 0,25 1 ErAa 0,25 1 ErAa 0,25 12 JaMo 0 91

1 ACCEPTED SOLUTION
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] )
)``````
2 REPLIES 2
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] )
)``````
Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.