max value of software version string

[wdrain]

Hi 

I am trying get the max and min values from a string column using a dax forumula. The Version column contains the software version and I would like to get the max value from this column. Would anybody know how to acomplish this with a dax formula ? My current dax forumla see below is incorrect and is not returning the max software version which should be 18.06.0.00. Any help would be much appreciated.

[Anonymous]

@wdrain, 

You can use FIRSTNONBLANK() described in this article. Try this measure: 

Latest Software Version =
CALCULATE (
    FIRSTNONBLANK ( 'Installed Software'[Version], 0 ),
    ALLEXCEPT ( 'Installed Software', 'Installed Software'[Software Product] )
)

[wdrain]

Aplogies Nick,

I sent the wrong number. the Latest version should be 19.00 instead of what I previously posted.

Thanks.

[Anonymous]

@wdrain, 

I did a little digging, your formula should be correct as long as you keep the version column format consistent, for example "##.##.##.#", adding 0 in front of a single digit number seems to do the trick for me (09.31.00.0). 

[wdrain]

Thanks Nick that make senses, however the data that is pulled is not normalized is there a way to normalize it in powerbi possibly with a dax formula so that it meets that format ?

[Anonymous · Accepted solution]

@wdrain, 

Here is an example you could use Power Query to add in a leading zero. Copy and Paste this code into a blank query for referece:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMteNyixQ0oHTlnrGFnoGBnoGSrE6mLKGQDlT/NJm+KUNFJJSSxKVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Software Product" = _t, Software = _t, Version = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Version", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Version.1", "Version.2", "Version.3", "Version.4"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Leading", each Text.PadStart([Version.1],2,"0"), Text.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Version.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Leading", "A"}, {"Version.2", "B"}, {"Version.3", "C"}, {"Version.4", "D"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Merged", each if [D] <> null then Text.Combine({[A],[B],[C],[D]}, ".") else Text.Combine({[A],[B]}, "."), Text.Type),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"B", "C", "D", "A"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Merged", "Version"}})
in
    #"Renamed Columns1"

[wdrain]

Nick 

Thanks very much for the quick reply. I tried that formula but still was not able to get the latest version. Any recommendation on how to get it display version 18.06.00.0 ?

Regards