Max value with multiple criteria

[Anonymous]

Hi All,

I have a table like below. I want to get the maximum value from the same grid and within the same week, but the expected result that the value displayed in the max value column is IP Name. Grid's name is very unique.

Week	Grid	IP Name	Value	Max Grid
1	H	A	4	A
1	I	A	3	A
1	J	C	1	A
1	J	A	4	A
1	J	B	3	A
1	K	C	5	C
1	K	A	4	C
2	L	D	1	A
2	L	C	2	A
2	L	A	4	A
2	M	E	5	E

Any help would be great. Ready to share my data and/or pbix file if needed.

Thanks

[amitchandak · Accepted solution]

@Anonymous , Assuming you need the last column

Create a new column

max Grid =
var _max = maxx(filter(Table,[Week] = earlier([Week]) && [Grid] = earlier([Grid])),[value])
return
maxx(filter(Table,[Week] = earlier([Week]) && [Grid] = earlier([Grid]) && [value] =_max ),[ IP Name])

[Anonymous]

Hi @amitchandak

Will you help again? What if I put another filter in the EXP column, where if the value "BB" is found, that value will be excluded. So the results obtained in the column "new max grid" are as follows :

Week	Grid	IP Name	Value	Exp	Max Grid	New Max Grid
1	H	A	4	AA	A	A
1	I	A	3	AA	A	A
1	J	C	1	AA	A	B
1	J	A	4	BB	A	B
1	J	B	3	AA	A	B
1	K	C	5	AA	C	C
1	K	A	4	AA	C	C
2	L	D	1	AA	A	C
2	L	C	2	AA	A	C
2	L	A	4	BB	A	C
2	M	E	5	AA	E	E

Thanks

[Anonymous]

Hi @amitchandak

Thanks for your helping me.

How about lookupvalue? is it possible to use it?