Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

MAX value separately by one column's different values

Hi

 

I would like to have a DAX to solve this (I could create a helper table quite easily in QE). Example table:

 

CountryCost per itemDate

US

 

1001.1.2021
US1001.2.2021
US1201.3.2021
GB10001.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).

Help
  • Anonymous's avatar
    Anonymous
    4 years ago

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

2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    Anonymous 


    Use this measure:

    Cost per item by country = 
CALCULATE(  
    MAX(Table[Cost per item]),
    REMOVEFILTERS(Table[Date])
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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