Forum Discussion
DAX return value based on Max value in a new column
Hi ARMHCI
You can try the followingAge_Max_assets_per_family_ID = VAR CurrentIDTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Family ID] ) ) VAR MaxAsset = MAXX ( CurrentIDTable, Data[Assets] ) VAR Result = MAXX ( FILTER ( CurrentIDTable, Data[Assets] = MaxAsset ), Data[Family_age] ) RETURN ResultIn case of duplicates this code will return the maximum age. We can return the minumum or concatinate the results upon your requirement. Please let me know is this is ok with you.
Super UserARMHCI , create two new columns
Rank = rankx(filter(Table, Table[Family ID] = earlier([Family ID])), [Assets])
Age_Max_assets_per_family_ID =
var _min = minx(filter( Table, Table[Family ID] = earlier([Family ID]) && [Rank] =1), [ID])
return
maxx(filter(Table, Table[ID] = _min && Table[Family ID] = earlier([Family ID])), [Family_age])
Community ChampionHi ARMHCI
You can try the following
Age_Max_assets_per_family_ID =
VAR CurrentIDTable =
CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Family ID] ) )
VAR MaxAsset =
MAXX ( CurrentIDTable, Data[Assets] )
VAR Result =
MAXX ( FILTER ( CurrentIDTable, Data[Assets] = MaxAsset ), Data[Family_age] )
RETURN
Result
In case of duplicates this code will return the maximum age. We can return the minumum or concatinate the results upon your requirement. Please let me know is this is ok with you.
Thank you, your solution worked too
