Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JD193704
Frequent Visitor

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

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.

JD193704_3-1687265936219.png

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!

1 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.

View solution in original post

7 REPLIES 7
SanderVeeken
Helper III
Helper III

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.

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?

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.

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

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
Helper III
Helper III

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

DAX would be the preferred way!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors