Forum Discussion
Max value with multiple criteria
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
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])
3 Replies
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])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