How to apply a max value to rows based on Unique identifier?

[NaWhite72]

How to get the max value in a measure and apply to all rows?

Hi everyone!

Let's say I have a table that looks like the result below. 

ID	Maximum Value	Amount
Unique1	5	$6
Unique1	1	$5
Unique2	1	$3
Unique2	4	$1

But, I want to create a dimension or measure that looks at all the values in the "Maximum Value" column, and only use that value based on the unique ID... Thus making the table look like the output below.

ID	Maximum Value	Amount
Unique1	5	$6
Unique1	5	$5
Unique2	4	$3
Unique2	4	$1

I tried using Aggr(max(Maximum Value), ID) but this didn't work, I also tried FirstSortedValue, and this did not work either... Does anyone have any ideas?

Thank you!

[NaWhite72]

Hi everyone!

Let's say I have a table that looks like the result below. 

ID	Maximum Value	Amount
Unique1	5	$6
Unique1	1	$5
Unique2	1	$3
Unique2	4	$1

But, I want to create a dimension or measure that looks at all the values in the "Maximum Value" column, and only use that value based on the unique ID... Thus making the table look like the output below.

ID	Maximum Value	Amount
Unique1	5	$6
Unique1	5	$5
Unique2	4	$3
Unique2	4	$1

I tried using Aggr(max(Maximum Value), ID) but this didn't work, I also tried FirstSortedValue, and this did not work either... Does anyone have any ideas?

Thank you!

[NaWhite72]

Hi everyone!

Let's say I have a table that looks like the result below. 

ID	Maximum Value	Amount
Unique1	5	$6
Unique1	1	$5
Unique2	1	$3
Unique2	4	$1

But, I want to create a dimension or measure that looks at all the values in the "Maximum Value" column, and only use that value based on the unique ID... Thus making the table look like the output below.

ID	Maximum Value	Amount
Unique1	5	$6
Unique1	5	$5
Unique2	4	$3
Unique2	4	$1

I tried using Aggr(max(Maximum Value), ID) but this didn't work, I also tried FirstSortedValue, and this did not work either... Does anyone have any ideas?

Thank you!

[Digvijay_Singh · Accepted solution]

This works if you got amount as dimension, do you need to use aggregation for amount value with your actual data?

Max(Total<ID>[Maximum Value])

[NaWhite72]

Thank you so much! This is exactly what I needed!

[sidhiq91]

@NaWhite72  You can also solve the same in the Script level itself.

NoConcatenate
Temp:
Load * inline [
ID,Value, Amount
Unique1, 5, $6
Unique1, 1, $5
Unique2, 1, $3
Unique2, 4, $1
];

left join (Temp)
Max_Value:
Load ID, Max(Value) as Max_Value
Resident Temp
Group by ID;

Drop field Value from Temp;

Exit Script;