Skip to main content
cancel
Showing results for 
Search instead 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

Reply
powerbihelp87
Helper III
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:

 

MaterialCategory ACategory  BCategory CCategory DDiff 
1234.8NANA.1.7 
12345NANA.109.19890.0899 
872981.3NANA.011.29 
       
       
       
1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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

 

Capture4.JPGCapture3.JPG

 

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.

View solution in original post

7 REPLIES 7
v-juanli-msft
Community Support
Community Support

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

 

Capture4.JPGCapture3.JPG

 

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.

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
Super User
Super User

@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 

thank you!

Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
camargos88
Community Champion
Community Champion

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)
 
Capture.PNG
Ricardo


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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