Regarding Max Condition with multiple condition

[Sridharkrish]

Hi All,
I have a table consisting of name, CRM number and a phase where I need to find the max value of the CRM number. I have three conditions here : I want DAX in PowerBi.
If a name has only agreements in the phase then the max value should be of the highest CRM number in agreement.
If a name consists of agreements, quotes and leads then also the max value should be of the highest CRM number in agreement.
If a name doesnt have any agreements but has leads and quotes then the CRM should of the highest CRM number.

I have tried many methods but couldnt find any solution please help me in this regard and its urgent !!
I have attached a snap of the example, please have a look at it how the result should look !!

[FreemanZ · Accepted solution]

hi @Sridharkrish 

try to add a column with this:

MaxValue =

VAR _MaxValue1 =

CALCULATE(

    MAX(TableName[CRM]),

    ALLEXCEPT(TableName, TableName[Name])

)

VAR _MaxValue2 =

CALCULATE(

    MAX(TableName[CRM]),

    ALLEXCEPT(TableName, TableName[Name]),

    TableName[Phase]="Agreement"

)

RETURN

    IF(

        _MaxValue2<>0,

        IF(TableName[CRM]=_MaxValue2, "MAX"),

        IF( TableName[CRM]=_MaxValue1, "MAX")

    )

i tried and it worked like this:

[Sridharkrish]

Thank you very much for your help. It got solved !!