DAX HELP WITH MAX WITH FEW CONDITIONS

[Sridharkrish]

Hi Everyone,

I have a table with CRM number, Name and Phase. I need to find max value with respect to the highest CRM number of the Quote (phase) alone. Though the name may consists of many other phases like agreement or leads but I need max value of highest number of the Quote. I have added a table with this message of how the result may look like. Thank you in advance.
I want DAX in PowerBi, I tried many other methods. Please help in this regard.
@FreemanZ 

CRM	Name	Phase	Max Value
1123	Rahul	Agreement
1130	Rahul	Agreement
1125	Rahul	Agreement
2240	Giva	Agreement
2241	Giva	Agreement
2247	Giva	Agreement
2250	Giva	Quote	MAX
2280	Giva	Lead
3300	Kicha	Quote
3301	Kicha	Quote	MAX
3302	Kicha	Lead

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your feedback, and please check the below picture and the attached pbix file.

Max indicator of CRM number: =
VAR _maxcrmvalue =
    MAXX (
        FILTER (
            ALL ( Data ),
            Data[Name] = MAX ( Data[Name] )
                && Data[Phase] = "Quote"
        ),
        Data[CRM]
    )
RETURN
    IF ( MAX ( Data[CRM] ) = _maxcrmvalue, "MAX" )

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

The below is for creating a new column.

Max CRM number indicator CC = 
VAR _maxcrmvalue =
    MAXX (
        FILTER (
            Data ,
            Data[Name] = EARLIER( Data[Name] )
                && Data[Phase] = "Quote"
        ),
        Data[CRM]
    )
RETURN
    IF ( Data[CRM]  = _maxcrmvalue, "MAX" )

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Max value of CRM number: =
IF (
    HASONEVALUE ( Data[Name] ),
    MAXX ( FILTER ( Data, Data[Phase] = "Quote" ), Data[CRM] )
)

[Sridharkrish]

Hi,
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 there shouldnt be any max value.
If a name consists of agreements, quotes and leads then the max value should be of the highest number of the CRM in the quote as per particular name.
If a name consists of multiple Quotes in CRM then they should be of the highest number of CRM in the quote. I have attached an example of how the value should be displayed for your refererance.

I have tried many methods but couldnt find any solution please help me in this regard.

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your feedback, and please check the below picture and the attached pbix file.

Max indicator of CRM number: =
VAR _maxcrmvalue =
    MAXX (
        FILTER (
            ALL ( Data ),
            Data[Name] = MAX ( Data[Name] )
                && Data[Phase] = "Quote"
        ),
        Data[CRM]
    )
RETURN
    IF ( MAX ( Data[CRM] ) = _maxcrmvalue, "MAX" )

[Sridharkrish]

Can I know how to add this as a column but not as a measure.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

The below is for creating a new column.

Max CRM number indicator CC = 
VAR _maxcrmvalue =
    MAXX (
        FILTER (
            Data ,
            Data[Name] = EARLIER( Data[Name] )
                && Data[Phase] = "Quote"
        ),
        Data[CRM]
    )
RETURN
    IF ( Data[CRM]  = _maxcrmvalue, "MAX" )

[Sridharkrish]

Thanks this worked out as well !

[FreemanZ · Accepted solution]

Hi @Sridharkrish 

You may also try this code:

MaxValue =

VAR _name = [Name]

VAR _max = MAXX(

    FILTER(

        'Table',

        'Table'[Name] = _name&&'Table'[Phase ] = "Quote"

    ),

    'Table'[CRM]

)

RETURN IF([CRM]=_max, "MAX")

i tried and it worked like this:

[Sridharkrish]

This also wroked out thanks !