Forum Discussion

RielLife's avatar
RielLife
Frequent Visitor
8 years ago

How to Filter table based on Max value of column

  Hi experts, I need help with something Im trying to figure out all day... I have a table as shown in the image below: I have multiple "medewerkers" as show in the first column, which have a...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
RielLife's avatar
RielLife
Frequent Visitor
8 years ago

v-danhe-msftThank you very much for the explanation and the sugested solution!
One problem is that sometimes the "max"  value of the column volgnummer can be 3, 4 or 5 (thus not only 2).

Do you know how I can adjust the by you provided formula to achieve my desired result?

Table = CALCULATETABLE('ProfitDirectIndirect','ProfitDirectIndirect'[Urenregistreren]=2)


Regards,

RielLife

v-danhe-msft's avatar
v-danhe-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi RielLife,

You could modify the formula as this:

Table = CALCULATETABLE('ProfitDirectIndirect',FILTER('ProfitDirectIndirect','ProfitDirectIndirect'[Urenregistreren]=MAXX('ProfitDirectIndirect','ProfitDirectIndirect'[Urenregistreren])))

Hope it could help you.

 

Regards,

Daniel He

  • RielLife's avatar
    RielLife
    Frequent Visitor
    8 years ago

    Hi v-danhe-msft,

     

    Thanks. It comes closes but is not yet what I am looking for. Now it shows in the table only the row of the employee with the highest "volgnummer"... IT now needs to show the highest value by employee.

    Thus now I have this:

    test = CALCULATETABLE(ProfitDirectIndirect;FILTER('ProfitDirectIndirect';'ProfitDirectIndirect'[Column1.Volgnummer]=MAXX('ProfitDirectIndirect';'ProfitDirectIndirect'[Column1.Volgnummer]))) 


    ^ but I need an additional filter in the formula to do the max lookup on employee level (column name = employee). 


    Hope this is possiblee... 

     

    Best regards and thanks so much in advance,

     

    RielLife

     

    • v-danhe-msft's avatar
      v-danhe-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi RielLife,

      I could not understand the sentence "an additional filter in the formula to do the max lookup on employee level (column name = employee)", could you please post me your desired result if possible?

       

      Regards,

      Daniel He