cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Abdel_Spateof Frequent Visitor
Frequent Visitor

Find latest status of repetitive incident

Hello

This is my second participation in this forum; I am still learning how power BI works; 

Thanks for helping me with the below case

I have 2 Tables, 

1) TimeTracking Table containing Worklogs 

KeyWorklog IDWorklog Creation DateTime Entry Logged TimeSTATUS_Value?
IMIS-2051210709/10/2018 10:17:300.5666666667 
IMIS-2051210508/10/2018 18:38:510.9666666667 
IMIS-2061210608/10/2018 18:38:540.2 
IMIS-2051210408/10/2018 18:38:500.2833333333 
IMIS-2051210308/10/2018 16:38:281.1 
IMIS-2051210208/10/2018 16:38:270.15 
IMIS-2051210008/10/2018 15:04:180.1333333333 
IMIS-2051210108/10/2018 16:38:260.1333333333 
IMIS-2061209908/10/2018 14:56:440.7666666667 
IMIS-2061209808/10/2018 14:38:270.7 
IMIS-2061209708/10/2018 12:03:410.9 
IMIS-2051209608/10/2018 12:03:390.0666666667 
IMIS-2061209508/10/2018 11:04:340.0333333333 
IMIS-2061209408/10/2018 10:51:440.6166666667 
IMIS-2051209308/10/2018 10:38:240.2666666667 
IMIS-2051209208/10/2018 10:38:230.25 
IMIS-1961209105/10/2018 18:07:460.0666666667 
IMIS-1961209005/10/2018 18:07:461.033333333 
IMIS-1961208905/10/2018 18:07:461.583333333 
IMIS-1961208805/10/2018 13:39:040.45 
IMIS-1961208705/10/2018 11:39:390.15 
IMIS-1961208505/10/2018 11:14:480.0833333333 
IMIS-1961208605/10/2018 11:19:200.0833333333 
IMIS-1961208405/10/2018 10:38:411.066666667 
IMIS-1961208304/10/2018 18:51:390.8 

 

2) History Table containing the status Change

 

 

KeyCHANGE_onStatus_ValueChange_effective_from
IMIS-206statusElab-Review08/10/2018 17:06:27
IMIS-161statusReviewed08/10/2018 16:23:42
IMIS-197statusreview08/10/2018 15:04:28
TA-3statusTo do08/10/2018 14:39:02
TA-2statusTo do08/10/2018 14:38:47
TA-1statusTo do08/10/2018 14:38:12
IRC-20statusSelected08/10/2018 14:10:47
IMIS-206statusElaboration08/10/2018 11:12:44
IMIS-206statusScreen08/10/2018 10:13:10
IMIS-206statusOpen08/10/2018 10:12:17
IMIS-205statusScreen08/10/2018 9:33:38
IMIS-205statusOpen08/10/2018 9:33:32
    

 

I need to know what was the status of a key During entry of a worklog , what should be the best function that could search in the status table and provide me with the status?

you can find the full sample data in the link below

https://docs.google.com/spreadsheets/d/1VoANXMB_gTpNWORqgW7_Jm_u2gHCCGqLoh98sUSQzjc/edit?usp=sharing

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Find latest status of repetitive incident

@Abdel_Spateof

 

Try this column.

See attached file as well

 

Status =
MINX (
    TOPN (
        1,
        FILTER (
            History_Table,
            History_Table[Key] = Time_Tracking_Table[Key]
                && History_Table[Change_effective_from]
                < Time_Tracking_Table[Worklog Creation Date]
        ),
        [Change_effective_from], DESC
    ),
    [Status_Value]
)
3 REPLIES 3
Community Support Team
Community Support Team

Re: Find latest status of repetitive incident

Hi @Abdel_Spateof,

 

I'm a little confused about your requirement, could you share your desired output so that I can understand your logic and get the solution?

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Abdel_Spateof Frequent Visitor
Frequent Visitor

Re: Find latest status of repetitive incident

Hello @v-piga-msft

For the first Table; I need to fill the Column with a header in Red which represents the Status

 

in the first table, when a worklog ID Happens; it happens in during a time status ( Represented in Table 2)

 

for Every Worklog ID, I need to compare the worklog creation Date of that Key with the Change Effective From , 

 

e.g Worklog ID 12xx happened when Key XXXX was in "Screen"

Highlighted
Super User
Super User

Re: Find latest status of repetitive incident

@Abdel_Spateof

 

Try this column.

See attached file as well

 

Status =
MINX (
    TOPN (
        1,
        FILTER (
            History_Table,
            History_Table[Key] = Time_Tracking_Table[Key]
                && History_Table[Change_effective_from]
                < Time_Tracking_Table[Worklog Creation Date]
        ),
        [Change_effective_from], DESC
    ),
    [Status_Value]
)