Forum Discussion

powerbihelp87's avatar
powerbihelp87
Icon for Helper V rankHelper V
6 years ago
Solved

MIN and MAX else blank Row appears

I am trying to achieve a calculation where only the Max and Min value appear (so highest or lowest number in that category/material else show NA).  I tried various DAX formulas but still could not fi...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi powerbihelp87 

    Create measures

     

    min = CALCULATE(MIN('Table'[Value]),ALLEXCEPT('Table','Table'[Material]))

max = CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Material]))

modified value =
VAR mdvalue =
    IF (
        MAX ( [Value] ) = [max]
            || MAX ( [Value] ) = [min],
        MAX ( [Value] )
    )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Category] ),
        mdvalue,
        [max] - [min]
    )

     

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

powerbihelp87 

You can create a new column or measure like this

max(max(max([Category A],[Category B]),[Category C]),Category D) - 
min(min(min([Category A],[Category B]),[Category C]),Category D)

. But you make sure the Data type is the same. NA is not allowed 

  • powerbihelp87's avatar
    powerbihelp87
    Icon for Helper V rankHelper V
    6 years ago

    thank you!