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
Abdel_Spateof
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

@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]
)

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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"

@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]
)

Regards
Zubair

Please try my custom visuals

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.