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 even x.xx.xxxxx.x. Each column does not follow a specific mask. The dots separate the Major Version, Minor Version, Patch Version, and Build Version.

 

I have created a measure of the Mode of the version with a little bit of DAX.

 

I now need to compare the version in each row to the Quick Measure. The issue is that the version number is not a number (because it has too many decimal places) and it is not quite text so I cannot run Greater than or Equal to comparisons.

My failed Calculated Column:

 

 

 

SoftwareCompliance = If(('All Computers'[Software Version] >= 'All Computers'[CurrentSoftwareVersion]), True, False)

 

 

 

 

How can I create a calculated column that will tell me Yes the version is up to date or no the version is behind?

 

Example Data

Computer Name

Software InstalledSoftware VersionAntivirus InstalledAntivirus Version

Computer 1

True5.23.10503.0True5.6.1.157
Computer 2True5.28.11009.0True5.6.3.157
Computer 3False True5.6.1.308
Computer 4True5.30True4.18.3.7
Computer 5True5.29.11103.0True5.6.3.157
Computer 6False False 

 

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

8 Replies

  • 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

    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.