Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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 , 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
User | Count |
---|---|
93 | |
83 | |
77 | |
71 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |