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.
Hi guys,
I couldn't find the right answer, maybe you can help me.
I've got a table, see image below. It's possible that salary, bonus etc gets updated so I want to calculate with the most recent record of it. In this case, with the MAX ValidFrom.
For example, I want to calculate the salary:
Salaris = [Factor opslag] * CALCULATE(SUM(WorkerPositionEarningCodes[Amount]); WorkerPositionEarningCodes[EarningCodeId] = "Salaris" && MAX(WorkerPositionEarningCodes[ValidFrom])) All text in my formula written in black is allright. So the red part gives me the following error:
"A function ‘CALCULATE’ has been used in a true-false expression that is used as a table filter expression. This is not allowed."
How can I solve this? Or this isn't the right way?
Thanks in advance!
Solved! Go to Solution.
Hello @RemiAnthonise,
This can be restructured as:
Salaris = [Factor opslag] * CALCULATE(SUM(WorkerPositionEarningCodes[Amount]); WorkerPositionEarningCodes[EarningCodeId] = "Salaris"; FILTER(WorkerPositionEarningCodes,WorkerPositionEarningCodes[ValidFrom]=MAX(WorkerPositionEarningCodes[ValidFrom])))
Hope this works.
Regards.
You miss something, after the max.
you should reference the fgact table (code), like:
&& 'FactTable'[ID] = MAX('DimTable'[ID])
I hope I'm seeing things clearly here.
Hello @RemiAnthonise,
This can be restructured as:
Salaris = [Factor opslag] * CALCULATE(SUM(WorkerPositionEarningCodes[Amount]); WorkerPositionEarningCodes[EarningCodeId] = "Salaris"; FILTER(WorkerPositionEarningCodes,WorkerPositionEarningCodes[ValidFrom]=MAX(WorkerPositionEarningCodes[ValidFrom])))
Hope this works.
Regards.
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 |
---|---|
119 | |
105 | |
78 | |
74 | |
52 |
User | Count |
---|---|
145 | |
109 | |
107 | |
90 | |
64 |