Hi, all. This question is related to my previous question, but a bit more complex.
I am working on data more than 1 million rows, so I need to do this in Power Pivot.
The screenshot below is an example on how to do it in normal excel sheet.
Let's say I have 3 columns: A, B, C; and there are 3 unique values in column A (let's call this Label). I can get column F = maximum values of column B for each Label (i.e. Pivot table with A as rows and max B as output values). There are multiple cells in column B that equals to maximum value in each Label. So, for each Label again, I need to get minimum values of column C that corresponding to maximum value of B (column F). Sorry my explanation is confusing, please just see the screenshot.
I can do this in normal sheet for example by using G2 = MIN(FILTER(C:C,B:B=F2)). But I don't know how to do it in Power Pivot.
In my previous question, someone give solution by using =CALCULATE(MIN('Table'[C]),FILTER('Table','Table'=MAX('Table'))). But this time, I have kind of filter inside filter.
Any suggestion? Thank you in advance.
I am working on data more than 1 million rows, so I need to do this in Power Pivot.
The screenshot below is an example on how to do it in normal excel sheet.
Let's say I have 3 columns: A, B, C; and there are 3 unique values in column A (let's call this Label). I can get column F = maximum values of column B for each Label (i.e. Pivot table with A as rows and max B as output values). There are multiple cells in column B that equals to maximum value in each Label. So, for each Label again, I need to get minimum values of column C that corresponding to maximum value of B (column F). Sorry my explanation is confusing, please just see the screenshot.
I can do this in normal sheet for example by using G2 = MIN(FILTER(C:C,B:B=F2)). But I don't know how to do it in Power Pivot.
In my previous question, someone give solution by using =CALCULATE(MIN('Table'[C]),FILTER('Table','Table'=MAX('Table'))). But this time, I have kind of filter inside filter.
Any suggestion? Thank you in advance.