Forum Discussion
Helper VMIN 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 | |
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
camargos88Community 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)Ricardo
powerbihelp87Thank 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!! 😁
- amitchandak
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
- 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.
v-juanli-msftCommunity 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.- 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!!
