Direct and Import Tables for Calculated Measure

[Aesir_1103]

Hello Experts,

I'm still learning Powerbi and I struggling in the calculation when working on a direct query and import query.

I have two tables Rates and Timesheet they have both dates and I need to perform a calculation in Timesheet table. to get the rates of the employee on a specific project on a specific date.

First I'm trying to calculate rate but it is not giving me the correct rates.

RATE CC2 = CALCULATE(SUM(Rates[Rate]),ALLEXCEPT(Timesheet,Timesheets[Key ID]))

Table: Rates	Import Table
			Date	Rate	KEYID
	EMP1	PR001	1/1/2023	20	EMP1PR0014492720
	EMP1	PR002	1/1/2023	10	EMP1PR0024492710
	EMP2	PR001	1/1/2023	20	EMP2PR0014492720
	EMP3	PR001	1/2/2023	30	EMP3PR0014492830
	EMP4	PR002	1/2/2023	10	EMP4PR0024492810
Table: Timesheet
Direct Query	Employee	Project	Date	Hours	KEYID
	EMP1	PR001	1/1/2023	4	EMP1PR0014492720
	EMP1	PR002	1/1/2023	4	EMP1PR0024492710
	EMP2	PR001	1/1/2023	8	EMP2PR0014492720
	EMP3	PR001	1/2/2023	8	EMP3PR0014492830
	EMP4	PR002	1/2/2023	8	EMP4PR0024492810

[v-yangliu-msft · Accepted solution]

Hi  @Aesir_1103 ,

Here are the steps you can follow：

1. Create measure.

Measure_Rate =
SUMX(
    FILTER(ALL(Rates),
    YEAR('Rates'[Date])=YEAR(MAX('Timesheet'[Date]))&&
    MONTH('Rates'[Date])=MONTH(MAX('Timesheet'[Date]))&&
    'Rates'[Employee]=MAX('Timesheet'[Employee])),'Rates'[Rate])

Measure =
[Measure_Rate] * MAX('Timesheet'[Hours])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

Please show the expected outcome based on the sample data you provided.

[Aesir_1103]

The expected output should be emp1 for jan 1 cost  120.

I need to do hours x rate per employee on each month. 

[lbendlin]

Don't include the rate in the composite key.  It's a fact column.