topic Re: Direct and Import Tables for Calculated Measure in DAX Commands and Tips

Direct and Import Tables for Calculated Measure

Aesir_1103 — Fri, 03 Feb 2023 08:24:37 GMT

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

Re: Direct and Import Tables for Calculated Measure

lbendlin — Sat, 04 Feb 2023 19:21:14 GMT

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

Re: Direct and Import Tables for Calculated Measure

Aesir_1103 — Sun, 05 Feb 2023 05:50:04 GMT

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

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

Re: Direct and Import Tables for Calculated Measure

lbendlin — Sun, 05 Feb 2023 15:32:47 GMT

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

Re: Direct and Import Tables for Calculated Measure

Anonymous — Mon, 06 Feb 2023 01:50:16 GMT

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