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

mmarbut's avatar
mmarbut
Frequent Visitor
6 years ago

For anyone else creeping on this post here are some additional pieces I have added

 

Two decimal place comparison:

Columnname = 
var soft = SUBSTITUTE('Table'[Software Version],".","|")
var target = SUBSTITUTE('Table'[Target Version],".","|")
return switch(true
,soft="N/A",false
,soft="",false
,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)),true
,false)

Notice: the top two rows in the switch are some catches for data that doesn't conform to the model.

 

Four Decimal Variant with the initail soft="N/A" switch.

Column Name = 
var soft = SUBSTITUTE('Table'[Software Version],".","|")
var target = SUBSTITUTE('Table'[Target Version],".","|")
return switch(true
,soft="N/A",false
,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)

 

The switch was really a great idea because I can adapt it to any circumstance, for instance one version type I am going to deal with returns some letters.