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

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.

lbendlin's avatar
lbendlin
Super User
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" )

  • mmarbut's avatar
    mmarbut
    Frequent Visitor
    6 years ago

    That works very well thank you very much.

     

    I was trying to pre-determine the "Current" software version based on the Mode of software version, but when I do that with your Query I get a Circular Dependancy Error.

     

    However; I am now toying with the idea of having the end-user define which version he WANTS to be the Software target version. So this solution is perfect for that use-case.

     

    Thank you very much.