Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Maybe it's because it's monday or something but I just can't get my head around a what I thought was a simple stuff but here I am, few hours later...
Problem:
I have two tables with no active relationship
First table is my fact table which holds all data related to punched hours
FactTable
Date | Hours | EmployeeId |
29.12.2020 | 8 | A |
30.12.2020 | 8 | A |
31.12.2020 | 4 | A |
1.1.2021 | 4 | A |
2.1.2021 | 8 | A |
My second table has information on salary rates per employee with a given start date like so:
SalaryTable
EmployeeId | SalaryFromDate | Rate |
A | 1.1.2020 | 50 |
A | 1.1.2021 | 100 |
B | 1.1.2009 | 60 |
My desired result
Using Dax measure I want to calculate the cost for each hours that is actually punched in and worked.
I'm trying to use this for example
Rate_Hourly =
VAR _salaryfromdate =
SELECTEDVALUE ( SalaryTable[SalaryTableFromDate] )
RETURN
CALCULATE (
SUM ( SalaryTable[hourlySalaryTable] ),
FILTER (
FactTable,
FactTable[date] >= _salaryfromdate
&& FactTable[date] <= TODAY ()
)
)
But this just gives me sum of all Hour rates.
How can I make sure I find the correct Hour rate within the date range given from FactTable and compare to SalaryTable?
Kindest
I tried this and see if it works for you
No relationships created
Measure
Rate_Hourly =
var _Rate = MAXX(
FILTER( SalaryTable, SalaryTable[EmployeeID] = SELECTEDVALUE(FactTable[EmployeeID])
&& SalaryTable[SalaryFromDate] <= SELECTEDVALUE(FactTable[Date]))
, SalaryTable[Rate]
)
return _Rate
FYI: I prefer to have a calculated column than measure if it is a large dataset
...
@sevenhills Thanks for the prompt reply and taking the time to help out!
I've successfully implemented the DAX code and it works. However it breaks at another granular level when I'm not looking at dates. See how the total does not sum up
Can I somehow make this work without having to use ISINSCOPE() or other similar functions?
Or should I just do as you mentioned and use a column?
Kind regards
Please can you provide what is expected output in your top scenario (like a mockup)
Tough to visualize sometimes ...
The expected output is to find hourly rate and multiply that number with the amount of hours worked. That should define the cost. However I need to be able to look at cost regardless of if I'm looking at one employee, two or all.
Come to think of it maybe it's easier to let PQ calculate the cost in the fact table? So everytime an hour is registered another column is calculated for the cost behind that hour
If my analysis of your problem is wrong, then I need more details.
Optionally, you can take a look
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |