Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Binara
Frequent Visitor

Min/MAX column with numeric maximum value

Hello all,

 

I am trying to add a cusom column to calculate the Max and Min for 3 columns(More in the future). Issue is that the data is alphanumeric for Cancelled values. So when I try it, I get the maximum value in letters. Is there anyway to get the highest numeric value as the Max, in this situation. Here is the sample table I am trying to work on. 

 

Binara_0-1667554742756.png

 

As you can see, I get the MAX as "Cancelled", but I am trying to get "23" as the result.

 

I am using this basic DAX -  

 

List.Max ({[D1],[D2],[D3]})

 

I am rather new to this, so I might be asking something very simple. Any assistance is much appreciated. Thank you,

 

Binara

1 ACCEPTED SOLUTION
m_alireza
Solution Specialist
Solution Specialist

Hi @Binara ,

Try copying this query in your advanced editor:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRX0lHKK83JgVGxOtFKRsZAnnNiXnJqTk5qCpBtaKkUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [D1 = _t, D2 = _t, D3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"D1", type text}, {"D2", type text}, {"D3", type text}}),
    Custom1 = Table.AddColumn(#"Changed Type", "Lists", each Text.Split(Text.Combine({[D1],[D2],[D3]},"|"),"|")),
    Custom2 = Table.AddColumn(#"Custom1", "MaxVal", each List.Max(List.Transform([Lists], each try Number.FromText(_) otherwise null))),
    Custom3 = Table.AddColumn(#"Custom2", "MinVal", each List.Min(List.Transform([Lists], each try Number.FromText(_) otherwise null))),
    #"Removed Columns" = Table.RemoveColumns(Custom3,{"Lists"})
in
    #"Removed Columns"

 

 Sample output:
minmax with mix.png

View solution in original post

2 REPLIES 2
Binara
Frequent Visitor

Hello @m_alireza 

 

Thank you so much. I was able to make it work! 🙂

 

Binara

m_alireza
Solution Specialist
Solution Specialist

Hi @Binara ,

Try copying this query in your advanced editor:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRX0lHKK83JgVGxOtFKRsZAnnNiXnJqTk5qCpBtaKkUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [D1 = _t, D2 = _t, D3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"D1", type text}, {"D2", type text}, {"D3", type text}}),
    Custom1 = Table.AddColumn(#"Changed Type", "Lists", each Text.Split(Text.Combine({[D1],[D2],[D3]},"|"),"|")),
    Custom2 = Table.AddColumn(#"Custom1", "MaxVal", each List.Max(List.Transform([Lists], each try Number.FromText(_) otherwise null))),
    Custom3 = Table.AddColumn(#"Custom2", "MinVal", each List.Min(List.Transform([Lists], each try Number.FromText(_) otherwise null))),
    #"Removed Columns" = Table.RemoveColumns(Custom3,{"Lists"})
in
    #"Removed Columns"

 

 Sample output:
minmax with mix.png

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.