Calculate hours worked with category rate from other table

[WimBi]

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

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

Thanks in advance – Willem

[johnt75 · Accepted solution]

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]

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