Forum Discussion

Sridharkrish's avatar
Sridharkrish
New Member
3 years ago
Solved

Regarding Max Condition with multiple condition

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 !!

 

PowerBI DAX
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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:

     


     

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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: