Detect a Column value change and increment another value

[Prima_]

Hi,

Whenever the version is changed in Table B, the Upgraded_sites in Table A should be incremented. Week is a filter. In this case, Week 2, 3 and 4 are selected. Table B shows, two sites in Australia has been upgraded. So, Upgraded_Sites in Table A shows 2 whereas none of the sites in Singapore is upgraded. So, upgraded_sites for SG in Table A shows 0. How do I create a measure for this? 

Thanks.

Table A

Table B

[Icey]

Hi @Prima_ ,

You may create two measures like below:

Version_is_Upgraded = 
VAR CurWeekVersion_ =
    MAX ( 'Table B'[version] )
VAR CurWeekVersion_Path =
    SUBSTITUTE ( CurWeekVersion_, ".", "|" )
VAR CurWeekVersion_1 =
    PATHITEM ( CurWeekVersion_Path, 1, INTEGER )
VAR CurWeekVersion_2 =
    PATHITEM ( CurWeekVersion_Path, 2, INTEGER )
VAR PreWeekVersion_ =
    CALCULATE (
        MAX ( 'Table B'[version] ),
        FILTER (
            ALLEXCEPT ( 'Table B', 'Table B'[Country], 'Table B'[Site_Name] ),
            'Table B'[week]
                = MAX ( 'Table B'[week] ) - 1
        )
    )
VAR PreWeekVersion_Path =
    SUBSTITUTE ( PreWeekVersion_, ".", "|" )
VAR PreWeekVersion_1 =
    PATHITEM ( PreWeekVersion_Path, 1, INTEGER )
VAR PreWeekVersion_2 =
    PATHITEM ( PreWeekVersion_Path, 2, INTEGER )
RETURN
    IF (
        PreWeekVersion_ <> BLANK (),
        ISAFTER (
            CurWeekVersion_1, PreWeekVersion_1, ASC,
            CurWeekVersion_2, PreWeekVersion_2, ASC )
    )

Upgraded_Site_Count = 
CALCULATE (
    DISTINCTCOUNT ( 'Table B'[Site_Name] ),
    FILTER ( 'Table B', [Version_is_Upgraded] )
) + 0

Note:

The "version" column's type is "Text". And I use SUBSTITUTE and PATHITEM functions to split version numbers in case you have more complex version numbers in your scenario. 

In addition, ISAFTER is a boolean function that emulates the behavior of a 'Start At' clause and returns true for a row that meets all of the condition parameters.

Referencce: How to compare version numbers in DAX - Microsoft Power BI Community

For more details, please check the attached .pbix file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Prima_]

Thanks a lot Icey for the code and .pbix file. I am using Power BI Desktop Version: 2.73.5586.1501 32-bit (September 2019). I guess ISAFTER() funtion is not supported in that.

[Icey]

Hi @Prima_ ,

You can also try this:

Version_is_Upgraded 2 =
VAR CurWeekVersion_ =
    MAX ( 'Table B'[version] )
VAR CurWeekVersion_Path =
    SUBSTITUTE ( CurWeekVersion_, ".", "|" )
VAR CurWeekVersion_1 =
    PATHITEM ( CurWeekVersion_Path, 1, INTEGER )
VAR CurWeekVersion_2 =
    PATHITEM ( CurWeekVersion_Path, 2, INTEGER )
VAR PreWeekVersion_ =
    CALCULATE (
        MAX ( 'Table B'[version] ),
        FILTER (
            ALLEXCEPT ( 'Table B', 'Table B'[Country], 'Table B'[Site_Name] ),
            'Table B'[week]
                = MAX ( 'Table B'[week] ) - 1
        )
    )
VAR PreWeekVersion_Path =
    SUBSTITUTE ( PreWeekVersion_, ".", "|" )
VAR PreWeekVersion_1 =
    PATHITEM ( PreWeekVersion_Path, 1, INTEGER )
VAR PreWeekVersion_2 =
    PATHITEM ( PreWeekVersion_Path, 2, INTEGER )
RETURN
    PreWeekVersion_ <> BLANK ()
        && CurWeekVersion_1 > PreWeekVersion_1
        || ( CurWeekVersion_1 = PreWeekVersion_1
        && CurWeekVersion_2 > PreWeekVersion_2 )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Prima_]

Hi @Icey,

Getting the conversion error. Week field is Whole Number and the other fields are text.

Thanks.

[Icey]

Hi @Prima_ ,

Could you check the Data type of each column again. I can only reproduce this issue when I change the data type of “Week” column from "Whole Number" to "Text".

Best Regards,

Icey

[Prima_]

Week field is whole number. 

Thanks.

[Prima_]

[Icey]

Hi @Prima_ ,

If you could create a sample file with the same issue, please share me. 

If you could not due to data security issues, please check each variable returned in the expression and check the data type.

Reference: How to provide sample data in the Power BI Forum

Best Regards,

Icey

[Prima_]

Due to security reasons, can't share the data.

Now, getting this error

Data type of Version_is_Upgraded is True/False

Format: True/False

[Prima_]

Thank you. Let me try it out!

[amitchandak]

@Prima_ , 2 new columns

new column in Table B =
change =
var _1= countx(filter(TableB, [country] = earlier([Country]) && [site] = earlier([site]) && [week] = earlier([week]) -1 && [verison] <> earlier([verison])), [verison])
return
if(isblank(_1),0,1)

new column in tAble A
sumx(filter(TableA, TableA[Country] = TableB[Country]),[Change])