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.

camargos88's avatar
camargos88
Icon for Community Champion rankCommunity Champion
6 years ago

Hi powerbihelp87 ,

 

Just past this code on Transform Data -> Advanced Editor:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUdKzABIQpGeoFKsDkTBFEjSwBFOWFpYGYHkLcyNLkCZDPWOEKgOg3lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Material = _t, #"Category A" = _t, #"Category B" = _t, #"Category C" = _t, #"Category D" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", Int64.Type}, {"Category A", type text}, {"Category B", type text}, {"Category C", type text}, {"Category D", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Material"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Category"}}),
#"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Value", Text.Trim, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","",null,Replacer.ReplaceValue,{"Value"}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Value", {{"Value", type number}}, "en-US"),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type with Locale"," "," ",Replacer.ReplaceText,{"Category"})
in
#"Replaced Value1"

 

 

And create this measure:

 

Diff =
VAR _max = CALCULATE(MAX('Table (3)'[Value]);ALLEXCEPT('Table (3)';'Table (3)'[Material]))
VAR _min = CALCULATE(MIN('Table (3)'[Value]);ALLEXCEPT('Table (3)';'Table (3)'[Material]))
RETURN IF(HASONEVALUE('Table (3)'[Category]); SUM('Table (3)'[Value]); _max - _min)
 
Ricardo
powerbihelp87's avatar
powerbihelp87
Icon for Helper V rankHelper V
6 years ago

Thank you for helping out camargos88  my original question was a bit misleading, my original table is not structured like that, my table is actually in the same format as the transformation you mentioned. I was able to figure it out but decided to leave out the blank requirement! I appreciate your help!! 😁