Forum Discussion
Direct and Import Tables for Calculated Measure
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.
| 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 |
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
4 Replies
- lbendlin
Super User
Please show the expected outcome based on the sample data you provided.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.
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
