Forum Discussion

mmarbut's avatar
mmarbut
Frequent Visitor
6 years ago
Solved

Semantic Software Version Data Comparison

I have a table of information about computers in Power BI. Some of the columns in this sheet contain Semantic Version Numbers. These version number are formatted like this x.x.x.x or xx.x.xx.x or eve...
Need Help
  • lbendlin's avatar
    lbendlin
    6 years ago

    OK, here's the calculated column. It may look a bit crazy but there is method behind the madness.

     

    Goodenough = 
var soft = SUBSTITUTE(Table2[Software Version],".","|")
var target = SUBSTITUTE(Table2[Target Version],".","|")
return switch(true
,VALUE(pathitem(soft,1))>VALUE(pathitem(target,1)),true
,VALUE(pathitem(soft,1))<VALUE(pathitem(target,1)),false
,VALUE(pathitem(soft,2))>VALUE(pathitem(target,2)),true
,VALUE(pathitem(soft,2))<VALUE(pathitem(target,2)),false
,VALUE(pathitem(soft,3))>VALUE(pathitem(target,3)),true
,VALUE(pathitem(soft,3))<VALUE(pathitem(target,3)),false
,VALUE(pathitem(soft,4))>=VALUE(pathitem(target,4)),true
,false)

     

    To test it out you can use this query:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY69DsIwEINfpcpcnc75o53ZWUAsVYcMATGUoFDE6xNSVCXAeLbvs4dBbMN0e8w+Njs3edGKfTjNTxd9c/TxfgnXJB1cPPt5Fca2+ELyDUlFYMOKeDktgWA2dVQmT5PsCGDuP1FL6jepsgVNkATF1YU6qrOp+F2Y+egS8YtnllV9asY68m+zLRklbnwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column3 = _t, Column5 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

     

    (name it "Table2" )

lbendlin's avatar
lbendlin
Super User
6 years ago

What made you create the quick measure?  It is not useful in your scenario. Delete it and write a calculated column instead.

mmarbut's avatar
mmarbut
Frequent Visitor
6 years ago

lbendlin 

I made the measure because I thought I would need it to create it in order to calculate the column.

 

I still don't know how to compare the two strings in a calculated column.

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago
    SoftwareCompliance = [Software Version] >= [CurrentSoftwareVersion]
    • mmarbut's avatar
      mmarbut
      Frequent Visitor
      6 years ago

      Current Software Verison is the measure that you told me to get rid of.

       

      Also this won't work because Software Version and Current Software Version are both text. The extra decimal points prevent it from being classified as a number. The calculated column that you suggested returns all as True.

      • lbendlin's avatar
        lbendlin
        Super User
        6 years ago

        OK, let's try a different tack.  Let's assume your version numbers come in pairs of four.

         

        a.b.c.d

         

        where each of the letters can represent multiple digits.

         

        I would propose replacing the periods with pipes, and then using PATHITEM comparisons. something like

         

        SWITCH(FALSE(),a1<a2,b1<b2,c1<c2,d1<d2,TRUE())

         

        I'll take your sample version numbers and I'll give you a sample calcuation  for these.