topic MAX value separately by one column's different values in DAX Commands and Tips

MAX value separately by one column's different values

Anonymous — Mon, 27 Sep 2021 08:52:29 GMT

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).

Re: MAX value separately by one column's different values

Fowmy — Mon, 27 Sep 2021 09:17:48 GMT

Anonymous

Use this measure:

Cost per item by country = CALCULATE( MAX(Table[Cost per item]), REMOVEFILTERS(Table[Date]) )

Re: MAX value separately by one column's different values

Anonymous — Thu, 30 Sep 2021 07:56:25 GMT

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).