cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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 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
1 ACCEPTED SOLUTION
Community Support

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.

7 REPLIES 7
Community Support

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.

Helper III

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

Super User

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

Helper III

thank you!

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Champion

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

Proud to be a Super User!

Helper III

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors