How to apply multiple filters on one column and receive the MAX-value

[JD193704]

Hello,

I'm trying to apply multiple filters on different columns of a table and see the results in a new column. 

In the example below you can see three columns. Column one has very less different values, lets call them projects. Column 2 can contain numbers that appear more than once per project. In a perfect world there would be only one valid number in column 3 for one value of column 2.

But I would like to catch errors if there are different numbers in column 3 for one value in column 2.

As you can see for example one the "Project" in column 1 is "A". If you look at the value "2" in column 2 you can see that there are two different  values in column 3. That's not what it should be. So, I need to take those two values (5678, 5679) and get the max value as you can see in the result column.

Example two shows a similar case but with three line items for value "1" in column 2. Column 3 shows only one line with a number. But the number needs to be added in all three lines.

In Excel I would know how to solve it. In Column G you see the result which contains the following formula:

=IF(AND(D2=0;F1=F2);G1;IF(AND(D2=0;F2=F3);G3;IF(F1=F2;MAX(D1:D2);IF(F2=F3;MAX(D2:D3);D2))))

Please let me know if you have any idea how this can be implemented in Power BI.

Thank you!

[SanderVeeken]

Well since you already have a unique column key it's super easy, just create a new measure:

MyMeasure = MAX(TableName[Column 3])

and add it to a table that also has the key. It will only display unique values for the key anyway and your measure will make sure it shows only the maximum value.

[JD193704]

The key is not unique. There can be multiple line items with the same key as you can see for A-2 or B-1. But per one key there should only be the MAX number from column 3 shown in the result column. 

Did I not get your solution or does it need another implementation?

[SanderVeeken · Accepted solution]

The key is unique to the value you're looking for. That is, PowerBI groups your distinct keys together and then finds the max for those rows, which is what you seemed to ask for.

EDIT:

In case you absolutely need it as a calculated column instead of in a visual, you can do:

MyColumn = MAXX(FILTER(Table1, Table1[Column_1_2Key]=EARLIER(Table1[Column_1_2Key])), ([Column 3]))

But a measure seems quicker and easier. That's why I asked about your use case - if you just want to display the max value in a visual, use the first method.

[JD193704]

The calculated column is what I actually need. Thanks for the solution!

[SanderVeeken]

You're welcome! Be advised that if you use that column in a visual together with the key column, it will by default sum all the values for a given "key".

[SanderVeeken]

You could do this both in PowerQuery and in DAX, what is your use case?

[JD193704]

DAX would be the preferred way!