Semantic Software Version Data Comparison

[mmarbut]

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 Installed	Software Version	Antivirus Installed	Antivirus Version
Computer 1	True	5.23.10503.0	True	5.6.1.157
Computer 2	True	5.28.11009.0	True	5.6.3.157
Computer 3	False		True	5.6.1.308
Computer 4	True	5.30	True	4.18.3.7
Computer 5	True	5.29.11103.0	True	5.6.3.157
Computer 6	False		False

[mmarbut]

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.

[lbendlin]

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

[mmarbut]

@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]

SoftwareCompliance = [Software Version] >= [CurrentSoftwareVersion]

[mmarbut]

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]

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 · Accepted solution]

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]

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.