Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gudns
Frequent Visitor

How to get how much time a row was in the same state

So, my problem is:

 

I have a bunch of equal IDs, and I want to check when a value is changed in another column. When changed, I need to return how much time the value was the same before changing and say what it was and what it became:


Each ID appears for the same amount, and each one have a status column, here's an example with one of the IDs:

 

idstatusdate_check
1135381099869705xa
1135381099869705xb
1135381099869705xc
1135381099869705xd
1135381099869705ye
1135381099869705yf
1135381099869705yg
1135381099869705zh
1135381099869705zi
1135381099869705zj
1135381099869705zk
1135381099869705zl
1135381099869705zm

 

Basically, I need to check when X became Y, then return the difference of dates e and a (I already have these dates). The same for when Y becomes Z.

1 ACCEPTED SOLUTION

 

NewColumn = 
VAR CurrentStatus_ = Table1[status]
VAR FollowingDate_ =
    CALCULATE (
        MIN ( Table1[date_check] );
        Table1[date_check] > EARLIER ( Table1[date_check] );
        ALLEXCEPT ( Table1; Table1[id] )
    )
VAR FollowingStatus_ =
    CALCULATE (
        VALUES ( Table1[status] );
        Table1[date_check] = FollowingDate_;
        ALLEXCEPT ( Table1; Table1[id] )
    )
VAR Status_Transition_ = CurrentStatus_ <> FollowingStatus_
RETURN
    IF (
        Status_Transition_;
        VAR FollowingStatus_StartDate_ =
            CALCULATE (
                MIN ( Table1[date_check] );
                Table1[status] = FollowingStatus_;
                ALLEXCEPT ( Table1; Table1[id] )
            )
        VAR CurrentStatus_StartDate_ =
            CALCULATE (
                MIN ( Table1[date_check] );
                ALLEXCEPT ( Table1; Table1[id]; Table1[status] )
            )
        RETURN
            IF (
                NOT ISBLANK ( FollowingStatus_StartDate_ );
                FollowingStatus_StartDate_ - CurrentStatus_StartDate_
            )
    )

Code formatted with   www.daxformatter.com

View solution in original post

12 REPLIES 12
AlB
Super User
Super User

Hi @gudns,

Try creating a new calculated column:

 

DateDiffAtTransition =
VAR CurrentStatus_ = Table1[status]
VAR PreviousDate_ =
    CALCULATE (
        MAX ( Table1[date_check] );
        Table1[date_check]
            < EARLIER ( Table1[date_check] );
        ALL ( Table1[status] )
    )
VAR PreviousStatus_ =
    CALCULATE (
        VALUES ( Table1[status] );
        Table1[date_check] = PreviousDate_;
        ALL ( Table1[status] )
    )
VAR Status_Transition_ = CurrentStatus_ <> PreviousStatus_
RETURN
    IF (
        Status_Transition_;
        VAR PreviousStatus_StartDate_ =
            CALCULATE (
                MIN ( Table1[date_check] );
                Table1[status] = PreviousStatus_;
                ALL ( Table1[date_check] )
            )
        VAR CurrentStatus_StartDate_ = Table1[date_check]
        RETURN
            CurrentStatus_StartDate_ - PreviousStatus_StartDate_
    )

Code formatted with   www.daxformatter.com

 

 

gudns
Frequent Visitor

Hey @AlB, thanks for your response!

 

I tried but I'm getting the "circular dependancy" error. Any idea why?

@gudns 

I'd need to see the code you are using in other calculated columns. I'm guessing you use some form of CALCULATE that triggers context transition and that creates the circular dependency.

If date_check were the actual dates, without calculations, it should work. 

gudns
Frequent Visitor

I tried with only one block of IDs, just like the example I gave you and it works!

 

However, I have several "blocks" of IDs in the same table, and I have to check the status change of each block of ID.

 

Example: 

 

IDSTATUSDATE CHECK
1135428471827319xa
1135428471827319xb
1135428471827319xc
1135428471827319yd
1135428471827319ye
1135428471827319yf
1135428471827319zg
1135428471827319zh
1135428471827319zi
1135381099869705xa
1135381099869705xb
1135381099869705yc
1135381099869705yd
1135381099869705ye
1135381099869705zf
1135381099869705zg
1135381099869705zh

 

First I have to check considering the 1135428471827319 id and then 1135381099869705 and so on.

@gudns 

Try this:

 

DateDiffAtTransition =
VAR CurrentStatus_ = Table1[status]
VAR PreviousDate_ =
    CALCULATE (
        MAX ( Table1[date_check] );
        Table1[date_check] < EARLIER ( Table1[date_check] );
        ALLEXCEPT ( Table1; Table1[ID] )
    )
VAR PreviousStatus_ =
    CALCULATE (
        VALUES ( Table1[status] );
        Table1[date_check] = PreviousDate_;
        ALLEXCEPT ( Table1; Table1[ID] )
    )
VAR Status_Transition_ = CurrentStatus_ <> PreviousStatus_
RETURN
    IF (
        Status_Transition_;
        VAR PreviousStatus_StartDate_ =
            CALCULATE (
                MIN ( Table1[date_check] );
                Table1[status] = PreviousStatus_;
                ALLEXCEPT ( Table1; Table1[ID] )
            )
        VAR CurrentStatus_StartDate_ = Table1[date_check]
        RETURN
            IF (
                NOT ISBLANK ( PreviousStatus_StartDate_ );
                CurrentStatus_StartDate_ - PreviousStatus_StartDate_
            )
    )    

 

 

 

gudns
Frequent Visitor

@AlB 

 

That worked wonderfully. The last thing I'm trying to do is return the number on the row of the previous stat, not the new one.

@gudns 

I think you should be able to get to that with minor changes to the last version. Give it a go, it will be a good exercise. If it doesn't work let me know and we'll have a look later. If the posts are of help please consider kudoing accordingly.

Cheers 

 

gudns
Frequent Visitor

@AlB 

 

I really tried, but I just can't do it. I feel like I'm so close, but I can't figure it out.

 

NewColumn = 
VAR CurrentStatus_ = Table1[status]
VAR FollowingDate_ =
    CALCULATE (
        MIN ( Table1[date_check] );
        Table1[date_check] > EARLIER ( Table1[date_check] );
        ALLEXCEPT ( Table1; Table1[id] )
    )
VAR FollowingStatus_ =
    CALCULATE (
        VALUES ( Table1[status] );
        Table1[date_check] = FollowingDate_;
        ALLEXCEPT ( Table1; Table1[id] )
    )
VAR Status_Transition_ = CurrentStatus_ <> FollowingStatus_
RETURN
    IF (
        Status_Transition_;
        VAR FollowingStatus_StartDate_ =
            CALCULATE (
                MIN ( Table1[date_check] );
                Table1[status] = FollowingStatus_;
                ALLEXCEPT ( Table1; Table1[id] )
            )
        VAR CurrentStatus_StartDate_ =
            CALCULATE (
                MIN ( Table1[date_check] );
                ALLEXCEPT ( Table1; Table1[id]; Table1[status] )
            )
        RETURN
            IF (
                NOT ISBLANK ( FollowingStatus_StartDate_ );
                FollowingStatus_StartDate_ - CurrentStatus_StartDate_
            )
    )

Code formatted with   www.daxformatter.com

gudns
Frequent Visitor

@AlB 

 

Again, thank you so much for the atention you've been giving me. I've been learning so much trying to solve this problem with you. Your last code works, but the CurrentStatus_Start_Date_ is the MIN of the datas. Therefore, the only time the information is accurate, is on the first status change. When the second one occurs, the data should be the MIN + how many days was spent after that.

 

I'm trying to make your two formulas work together to solve this. Any insights?

gudns
Frequent Visitor

@AlB 

 

I actually got it. I used both of your formulas as the source of a third column. That column have everything I need: the days in the right places and the days of the current status. I named your columns "DatePreviousStatus" and "DateNewStatus". The code is probably a lot more convoluted than it should be, but since I'm a beginner, I'm pretty happy about it. Thank you again for your help.

 

TempostVaga =
VAR DateNothingChanged =
    CALCULATE (
        MIN ( consultaPastas[Date created] );
        ISBLANK ( consultaPastas[DatePreviousStatus] );
        ALLEXCEPT ( consultaPastas; consultaPastas[id] )
    )
VAR Real_Number_Of_Days_After_Transition_ =
    CALCULATE (
        MIN ( consultaPastas[Date created] );
        NOT ISBLANK ( consultaPastas[DateNewStatus] );
        ALLEXCEPT ( consultaPastas; consultaPastas[id] )
    )
VAR Real_Number_Of_Days_Before_Transition =
    CALCULATE (
        MAX ( consultaPastas[Date created] );
        NOT ISBLANK ( consultaPastas[DateNewStatus] );
        ALLEXCEPT ( consultaPastas; consultaPastas[id] )
    )
VAR Check_First_Change =
    CALCULATE (
        MIN ( consultaPastas[Date created] );
        NOT ISBLANK ( consultaPastas[DatePreviousStatus] );
        ALLEXCEPT ( consultaPastas; consultaPastas[id] )
    )
VAR Date_Of_First_Query =
    CALCULATE (
        MIN ( consultaPastas[Date created] );
        ALLEXCEPT ( consultaPastas; consultaPastas[id] )
    )
VAR Date_Aux =
    CALCULATE (
        MAX ( consultaPastas[Date created] ) + 1;
        NOT ISBLANK ( consultaPastas[DatePreviousStatus] );
        ALLEXCEPT ( consultaPastas; consultaPastas[id] )
    )
VAR DateOfLastQuery =
    MAX ( consultaPastas[Date created] )
RETURN
    IF (
        CALCULATE (
            MAX ( consultaPastas[DatePreviousStatus] ) = 0;
            ALLEXCEPT ( consultaPastas; consultaPastas[id] )
        );
        IF (
            consultaPastas[Date created] = DateOfLastQuery;
            DateOfLastQuery - DateNothingChanged
        );
        IF (
            NOT ISBLANK ( consultaPastas[DatePreviousStatus] );
            IF (
                consultaPastas[Date created] = Check_First_Change;
                Real_Number_Of_Days_After_Transition_ - Date_Of_First_Query;
                Real_Number_Of_Days_Before_Transition - Real_Number_Of_Days_After_Transition_
            );
            IF (
                consultaPastas[Date created] = DateOfLastQuery;
                DateOfLastQuery - Date_Aux
            )
        )
    )
gudns
Frequent Visitor

@AlB 

 

The date column contains the actual date values, it's not a calculated column. I can't find the problem.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.