Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
id | status | date_check |
1135381099869705 | x | a |
1135381099869705 | x | b |
1135381099869705 | x | c |
1135381099869705 | x | d |
1135381099869705 | y | e |
1135381099869705 | y | f |
1135381099869705 | y | g |
1135381099869705 | z | h |
1135381099869705 | z | i |
1135381099869705 | z | j |
1135381099869705 | z | k |
1135381099869705 | z | l |
1135381099869705 | z | m |
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.
Solved! Go to 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_ ) )
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_ )
Hey @AlB, thanks for your response!
I tried but I'm getting the "circular dependancy" error. Any idea why?
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.
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:
ID | STATUS | DATE CHECK |
1135428471827319 | x | a |
1135428471827319 | x | b |
1135428471827319 | x | c |
1135428471827319 | y | d |
1135428471827319 | y | e |
1135428471827319 | y | f |
1135428471827319 | z | g |
1135428471827319 | z | h |
1135428471827319 | z | i |
1135381099869705 | x | a |
1135381099869705 | x | b |
1135381099869705 | y | c |
1135381099869705 | y | d |
1135381099869705 | y | e |
1135381099869705 | z | f |
1135381099869705 | z | g |
1135381099869705 | z | h |
First I have to check considering the 1135428471827319 id and then 1135381099869705 and so on.
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_
)
)
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.
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
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_ ) )
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?
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 ) ) )
The date column contains the actual date values, it's not a calculated column. I can't find the problem.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |