topic Re: DAX return value based on Max value in a new column in DAX Commands and Tips

DAX return value based on Max value in a new column

ARMHCI — Tue, 22 Mar 2022 23:51:30 GMT

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

Re: DAX return value based on Max value in a new column

amitchandak — Wed, 23 Mar 2022 03:46:36 GMT

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

Re: DAX return value based on Max value in a new column

tamerj1 — Wed, 23 Mar 2022 03:58:22 GMT

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

Re: DAX return value based on Max value in a new column

ARMHCI — Wed, 23 Mar 2022 12:56:15 GMT

Thank you for the solution

Re: DAX return value based on Max value in a new column

ARMHCI — Wed, 23 Mar 2022 12:56:49 GMT

Thank you, your solution worked too

Re: DAX return value based on Max value in a new column

Whitewater100 — Wed, 23 Mar 2022 13:24:19 GMT

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

return

CALCULATE(MAX(Families[Family_age]),

FILTER(ALL(Families),Families[Family ID] = family &&

Families[Rank] = 1))