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 a...
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"
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

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

    • wdrain's avatar
      wdrain
      Helper I
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        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"