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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.