Power Pivot: Working on corresponding values of maximum values

[gifariz]

Hi all, new here. I am working on a data more than 1 million rows, so I need to work it on Power Pivot. Let's say this data has column A and B. When I make the pivot table of this data, I can get the maximum value of column A. Let's say column A has 4 cells containing this value. I want to get the minimum value among the 4 cells in column B corresponding those max of A.

I can do this in normal sheet. First, by counting the number of cells equal to max value of A: countMaxA=COUNTIF(A:A,maxA). Then, sort the range "A:B" by column A. Then, get the minimum of corresponding B by minCorrB=MIN(OFFSET(B1,MATCH(maxA,A:A,0)-1,0,countMaxA,1)).

But I dont know how to do this in Power Pivot. Any suggestion?

 

[mart37]

CALCULATE(MIN(Table1[val b]);FILTER(Table1;Table1[val a]=MAX(Table1[val a])))

 

[gifariz]

It works! Thank you very much. I just knew about DAX.