How to compare version numbers in DAX

Scenario: 

 

Suppose that some clients are running too old software versions and you need to find them before upgrading. 

  

How to achieve that: 

  

Sample data  

 

  

  

Tips: 

• Use SUBSTITUTE and PATHITEM to split version numbers  

https://docs.microsoft.com/en-us/dax/substitute-function-dax 

https://docs.microsoft.com/en-us/dax/pathitem-function-dax 

  

• Use ISAFTER to get the final list 

  

Steps:

      1. Add a measure that holds the target version number manually

 

 

 

Measure target version = "2.85.20.10" 

 

 

 

      2. Add a calculated table 

 

 

 

Table 2 =  

VAR target_version = [Measure target version] 

VAR target_version_path = 

    SUBSTITUTE ( target_version, ".", "|" ) 

VAR target_version_1 = 

    PATHITEM ( target_version_path, 1, INTEGER ) 

VAR target_version_2 = 

    PATHITEM ( target_version_path, 2, INTEGER ) 

VAR target_version_3 = 

    PATHITEM ( target_version_path, 3, INTEGER ) 

VAR target_version_4 = 

    PATHITEM ( target_version_path, 4, INTEGER ) 

RETURN 

    FILTER ( 

        'Table', 

        VAR version_path = 

            SUBSTITUTE ( 'Table'[Version], ".", "|" ) 

        VAR version_1 = 

            PATHITEM ( version_path, 1, INTEGER ) 

        VAR version_2 = 

            PATHITEM ( version_path, 2, INTEGER ) 

        VAR version_3 = 

            PATHITEM ( version_path, 3, INTEGER ) 

        VAR version_4 = 

            PATHITEM ( version_path, 4, INTEGER ) 

        RETURN 

            ISAFTER ( 

                version_1, target_version_1, DESC, 

                version_2, target_version_2, DESC, 

                version_3, target_version_3, DESC, 

                version_4, target_version_4, DESC ) 

    ) 

 

 

 

 

 

Now, you know client B/D/F should update version to at least “Measure target version” 2.85.20.10 

   

See attached file for details. 

 

Author: Sam Zha 

Reviewer: Kerry & Ula