Filter for max value based upon another column

[Anonymous]

Hi,

I would like to filter for the max value in one column based upon another column which is a project name over a range of time.

Basically I want to find the max total employees for each project which has multiple entries and create a new table with just the max value for each project

 

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

One sample for your reference. Here we can create a calculated column as below to work on it.

max total = CALCULATE(MAX(Table1[Total Employees]),FILTER(Table1,Table1[Project name]=EARLIER(Table1[Project name])))

[Anonymous]

thank you very much

[edhans]

In Power Query

1. select the Project Name column
2. Select Group By on the home tab
3. Name the column - "Max of Employee Count" for example.
4. Select MAX for the operation
5. Select the Total Employees column.

The load that into the data model.