Forum Discussion

wdrain's avatar
wdrain
Helper I
7 years ago
Solved

max value of software version string

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.

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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"

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      wdrain
      Helper I
      7 years ago

      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

       

       

    • wdrain's avatar
      wdrain
      Helper I
      7 years ago

      Aplogies Nick,

       

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

       

      Thanks.

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

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