Max value of column based on certain text in another column

[Anonymous]

Hello,

I have a column 'X' with numerical values in it and another column 'Y' with text in it. In column 'Y' it will either contain the text 'A', 'B' or 'C'. I want to return the maximum value from column 'X' but with a filter so it only considers values where there is an 'A' in the row from the 'Y' column.

In power query I added a custom column with the formula:

IF text.contains([Y], "A") then [X] else 0

But when I then create a new measure with the DAX function 'MAX' for my new custom column it doesn't return the maximum value. It returns 98 when the actual max value is 1200. Is there a reason why it isn't searchin the whole column? When I look through my custom column in power query i can see a lot of numbers bigger than 98, but all of these seem to be ignored.

Can anyone help with this?

[harshnathani · Accepted solution]

Hi @Anonymous ,

Try this measure

Max Value = 

CALCULATE(
    MAX('Table'[X]),'Table[Y] = "A")
)

Regards,

Harsh Nathani

[Anonymous]

Hi @harshnathani,

This has worked, thanks for your help. It seems so simple now!

[Fowmy]

@Anonymous 

I am not sure about your Power Query code 
It easy to add the Column that gives you the MAX by category in the model than in Power Query.
Add the following column: DAX

Max Value = 

CALCULATE(
    MAX('Table'[x]),
    ALLEXCEPT('Table','Table'[y])
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn