Forum Discussion
DAX return value based on Max value in a new column
Hi,
I have the following table
| Number | Gender | GivenName | Surname | Family ID | Assets | Family_age |
| 1 | female | Anna | Friedman | 3001 | 15,000 | 52 |
| 2 | male | Lelio | Alonzo | 3001 | 10,000 | 51 |
| 3 | male | Lovre | Pavić | 3001 | 15,000 | 34 |
| 4 | female | Susanne | Isaksen | 3001 | 11,000 | 60 |
| 5 | male | Øystein | Holst | 3001 | 12,000 | 20 |
| 6 | female | Rosaura | Trentino | 3001 | 9,000 | 33 |
| 7 | male | Argimiro | Luna | 5001 | 20,000 | 60 |
| 8 | female | Albika | Godina | 5001 | 30,000 | 75 |
| 9 | male | Zain | Mitchell | 5001 | 33,000 | 31 |
| 10 | female | Tiziana | Sagese | 5001 | 10,000 | 22 |
| 11 | female | Dorottya | Hajdu | 5001 | 53,000 | 70 |
| 12 | male | Johan | Lennert | 5001 | 3,000 | 23 |
| 13 | male | Daris | Sore | 2001 | 10,000 | 63 |
| 14 | male | Falco | Barraza | 2001 | 100,000 | 50 |
| 15 | male | Benedetto | Greco | 2001 | 63,000 | 20 |
| 16 | male | Makoto | Numata | 2001 | 100,000 | 50 |
| 17 | female | Samira | Grigoryeva | 2001 | 81,000 | 35 |
| 18 | male | Alessio | Padovano | 2001 | 23,000 | 85 |
I would like to be able to create a column that contains the FAMILY_AGE corresponding to the Max value in ASSETS for a specific FAMILY_ID.
for example; for FAMILY_ID 3001 the max ASSETS is 15,000. That 15,000 correspond to 52 in FAMILY_AGE. Return in the new column Age_Max_assets_per_family_ID 52 in all rows that correspond to FAMILY ID 3001.
The result would look like something like this :
| Number | Gender | GivenName | Surname | Family ID | Assets | Family_age | Age_Max_assets_per_family_ID |
| 1 | female | Anna | Friedman | 3001 | 15,000 | 52 | 52 |
| 2 | male | Lelio | Alonzo | 3001 | 10,000 | 51 | 52 |
| 3 | male | Lovre | Pavić | 3001 | 15,000 | 34 | 52 |
| 4 | female | Susanne | Isaksen | 3001 | 11,000 | 60 | 52 |
| 5 | male | Øystein | Holst | 3001 | 12,000 | 20 | 52 |
| 6 | female | Rosaura | Trentino | 3001 | 9,000 | 33 | 52 |
| 7 | male | Argimiro | Luna | 5001 | 20,000 | 60 | 70 |
| 8 | female | Albika | Godina | 5001 | 30,000 | 75 | 70 |
| 9 | male | Zain | Mitchell | 5001 | 33,000 | 31 | 70 |
| 10 | female | Tiziana | Sagese | 5001 | 10,000 | 22 | 70 |
| 11 | female | Dorottya | Hajdu | 5001 | 53,000 | 70 | 70 |
| 12 | male | Johan | Lennert | 5001 | 3,000 | 23 | 70 |
| 13 | male | Daris | Sore | 2001 | 10,000 | 63 | 50 |
| 14 | male | Falco | Barraza | 2001 | 100,000 | 50 | 50 |
| 15 | male | Benedetto | Greco | 2001 | 63,000 | 20 | 50 |
| 16 | male | Makoto | Numata | 2001 | 100,000 | 50 | 50 |
| 17 | female | Samira | Grigoryeva | 2001 | 81,000 | 35 | 50 |
| 18 | male | Alessio | Padovano | 2001 | 23,000 | 85 | 50 |
Be careful! for FAMILY_ID 2001 we have 2 similar ASSETS that correspond to 2 similar FAMILY_AGE. (see rows 14 and 16).
Thanks in advance for the help
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.
5 Replies
- amitchandak
Super User
ARMHCI , 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])- tamerj1
Community Champion
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.
Thank you, your solution worked too
Thank you for the solution
Whitewater100Solution Sage
Hello:
Continuing on the great answers already given, and building off of amitchandak reply you can try two columns:
Rank = rankx(filter(ALL(Families), Families[Family ID] = earlier(Families[Family ID])),[Assets])Then:Max Age/Family-Assets =var family = Families[Family ID]var topvalue = rankx(filter(ALL(Families), Families[Family ID] = earlier(Families[Family ID])),[Assets])returnCALCULATE(MAX(Families[Family_age]),FILTER(ALL(Families),Families[Family ID] = family &&Families[Rank] = 1))
