Groupby, sort and then count the number of changes

[HarkaranSingh]

Hi,

I am new to DAX and need to understand how to count the number of changes that occur after I have used group by and sort.

I have the following table:

ProductNo	VoyNo	rem	InTime	ChangeDate	Change
1	S1	340	15-01-2018 23:06	15-01-2018 21:00	0
1	S1	220	15-01-2018 23:06	15-01-2018 22:00	1
1	S1	110	15-01-2018 23:06	15-01-2018 21:00	1
1	S1	0	15-01-2018 23:06	15-01-2018 21:00	0
1	S2	200	17-12-2018 01:00	17-12-2018 00:00	0
1	S2	100	17-12-2018 01:00	17-12-2018 01:00	1
1	S2	0	24-12-2018 01:00	24-12-2018 01:00	0

Here, I have created the "change" column using R programming language and would like to know how to do it in DAX.

Logic is: First I have grouped by the three columns: ProductNo, VoyNo and InTime. Then I have sorted "rem" column in descending order and then everytime the datetime in "ChangeDate" column changes, it should input 1 in "change" column, if date remains the same as the previous one then it should show 0. CountDistinct doesn't work as sometimes date is changed and then changed again to the first date and countdistinct counts that only as 1 and not 2. Example below:

ProductNo	VoyNo	rem	InTime	ChangeDate	Change
1	S1	340	15-01-2018 23:06	15-01-2018 21:00	0
1	S1	220	15-01-2018 23:06	15-01-2018 22:00	1
1	S1	110	15-01-2018 23:06	15-01-2018 21:00	1
1	S1	0	15-01-2018 23:06	15-01-2018 21:00	0

After group by and sort, first value in "Change" is 0. Then date in "ChangeDate" column changes from row 1 to row 2 and value of 1 in "Change" column reflects that. Similarly, date changes again from row 2 to 3 and again value 1 of "change column reflect that.

Basically, I'm trying to calculatethe number of changes per group (based on the grouping and sorting criteria I described earlier) 

If it helps, this is how I did it in R

Table%>% group_by(ProductNo, VoyNo, InTime) %>% arrange(desc(rem)) %>%
mutate(change=if_else(ChangeDate!=lag(ChangeDate), 1, 0))

[Zubair_Muhammad · Accepted solution]

@HarkaranSingh 

Try this calculated column

It works with your sample data

Column =
VAR PreviousChangeDate =
    MINX (
        TOPN (
            1,
            FILTER (
                Table1,
                [ProductNo] = EARLIER ( [ProductNo] )
                    && [VoyNo] = EARLIER ( [VoyNo] )
                    && [InTime] = EARLIER ( [InTime] )
                    && [rem] > EARLIER ( [rem] )
            ),
            [rem], ASC
        ),
        [ChangeDate]
    )
RETURN
    IF (
        OR ( ISBLANK ( PreviousChangeDate ), [ChangeDate] = PreviousChangeDate ),
        0,
        1
    )

 

[HarkaranSingh]

Thanks for your help 🙂