MIN and MAX else blank Row appears

[powerbihelp87]

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 figure out the best way for this to appear?

Diff column: the difference between the highest and lowest value

Below is a sample dataset of how it should appear:

Material	Category A	Category  B	Category C	Category D	Diff
1234	.8	NA	NA	.1	.7
12345	NA	NA	.109	.19890	.0899
87298	1.3	NA	NA	.01	1.29

[v-juanli-msft · Accepted solution]

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.

[powerbihelp87]

thank you so much for looking into this.  @v-juanli-msft  the issue with the original requirements were that the categories are actually column names which wasn't best practice.  I was able to acheive what I wanted by rethinking the visuals.  thank you!!

[amitchandak]

@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]

thank you!

[Ashish_Mathur]

Hi,

What does your source data look like?  Share it in a format that i can paste it in an Excel workbook.

[camargos88]

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]

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