Max Value with multpile conditions (criteria based)

[SK87]

Hi Team,

I want maximum value of given attributes but there is some criteria that Value can't be below some number and greater than some number. I tried implementing DAX but results are not coming correct

Maximum Value of Last3 months =

VAR CA = SELECTEDVALUE ( 'Table'[A] )

VAR RefDate = MAX('Date'[SELECT MONTH])

VAR MaxValue = Calculate(MAX ( 'Table'[Value] ),

DATESINPERIOD('Previous Period'[SELECT MONTH],RefDate,-3,MONTH)

)

RETURN  

IF (

        CA = "A",  

        IF (

            MaxValue > 1000,

            Maxvalue & " ' "

        ),

IF (

        CA = "B",  

        IF (

            MaxValue > 30 || MaxValue < 250,

            MaxValue & " "" "

        ),

IF (

        CA = "C",  

        IF (

            MaxValue > 10 || MaxValue < 350,

            MaxValue & " "" "

        ),

          MaxValue & " "" "

    )))

Please suggest.

[v-yiruan-msft]

Hi @SK87 ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[v-yiruan-msft]

Hi @SK87 ,

Please update the formula of measure [Maximum Value of Last3 months] as below and check if it can return the correct result...

Maximum Value of Last3 months =
VAR CA =
    SELECTEDVALUE ( 'Table'[A] )
VAR RefDate =
    MAX ( 'Date'[SELECT MONTH] )
VAR MaxValue =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        DATESINPERIOD ( 'Previous Period'[SELECT MONTH], RefDate, -3, MONTH )
    )
RETURN
    IF (
        CA = "A"
            && MaxValue > 1000,
        Maxvalue & " ' ",
        IF (
            CA = "B"
                && MaxValue > 30
                && MaxValue < 250,
            MaxValue & " "" ",
            IF (
                CA = "C"
                    && MaxValue > 10
                    && MaxValue < 350,
                MaxValue & " "" ",
                MaxValue & " "" "
            )
        )
    )

If the above one not working, could you please provide the calculation logic of the above formula or the function which you want to achieve just like below sentences? It is better if you can give some sample data to explain it. Thank you.

IF XX THEN XX

IF XX THEN XX

IF XX THEN XX

Best Regards

[wdx223_Daniel]

maybe you want to use &&, instead of "||"

[SK87]

Didn't worked with that too. @wdx223_Daniel