Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
Hello:
Continuing on the great answers already given, and building off of amitchandak reply you can try two columns:
@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])
Thank you for the solution
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.
Thank you, your solution worked too
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |