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
I would like to have a DAX to solve this (I could create a helper table quite easily in QE). Example table:
Country | Cost per item | Date |
US
| 100 | 1.1.2021 |
US | 100 | 1.2.2021 |
US | 120 | 1.3.2021 |
GB | 1000 | 1.4.2021 |
What I would like to achieve?
- DAX would be called "Cost per item by country"
- Logic: the MAX Value by country regardless of the date
-Use case: this would be used as helper measure to calculate total cost ([Cost per item by country] * [Volume] -> I need to take into consideration country differences in cost price, but no timeline/date differences (just use the max of the country).
Solved! Go to Solution.
I think your suggestion only does the removal of time/date in the formula. In my model the main problem is that it that current formula suggestions do not do the calculations by row/country if my table does not have a country in it. So if I dont have country the MAX chooses the largest number of the whole table (without considering the country differences) and thus the total result is wrong. If country is a dimension in the table, then it calculates it correctly.
I did a workaround and created just a helper table for this and it of course did the trick. Maybe some day I know how to work these around directly with DAX (or understand when it does not make sense to try to tackle with DAX).
@FatherTheWizard
Use this measure:
Cost per item by country =
CALCULATE(
MAX(Table[Cost per item]),
REMOVEFILTERS(Table[Date])
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I think your suggestion only does the removal of time/date in the formula. In my model the main problem is that it that current formula suggestions do not do the calculations by row/country if my table does not have a country in it. So if I dont have country the MAX chooses the largest number of the whole table (without considering the country differences) and thus the total result is wrong. If country is a dimension in the table, then it calculates it correctly.
I did a workaround and created just a helper table for this and it of course did the trick. Maybe some day I know how to work these around directly with DAX (or understand when it does not make sense to try to tackle with DAX).
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 |
---|---|
40 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |