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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.