Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Key | Worklog ID | Worklog Creation Date | Time Entry Logged Time | STATUS_Value? |
IMIS-205 | 12107 | 09/10/2018 10:17:30 | 0.5666666667 | |
IMIS-205 | 12105 | 08/10/2018 18:38:51 | 0.9666666667 | |
IMIS-206 | 12106 | 08/10/2018 18:38:54 | 0.2 | |
IMIS-205 | 12104 | 08/10/2018 18:38:50 | 0.2833333333 | |
IMIS-205 | 12103 | 08/10/2018 16:38:28 | 1.1 | |
IMIS-205 | 12102 | 08/10/2018 16:38:27 | 0.15 | |
IMIS-205 | 12100 | 08/10/2018 15:04:18 | 0.1333333333 | |
IMIS-205 | 12101 | 08/10/2018 16:38:26 | 0.1333333333 | |
IMIS-206 | 12099 | 08/10/2018 14:56:44 | 0.7666666667 | |
IMIS-206 | 12098 | 08/10/2018 14:38:27 | 0.7 | |
IMIS-206 | 12097 | 08/10/2018 12:03:41 | 0.9 | |
IMIS-205 | 12096 | 08/10/2018 12:03:39 | 0.0666666667 | |
IMIS-206 | 12095 | 08/10/2018 11:04:34 | 0.0333333333 | |
IMIS-206 | 12094 | 08/10/2018 10:51:44 | 0.6166666667 | |
IMIS-205 | 12093 | 08/10/2018 10:38:24 | 0.2666666667 | |
IMIS-205 | 12092 | 08/10/2018 10:38:23 | 0.25 | |
IMIS-196 | 12091 | 05/10/2018 18:07:46 | 0.0666666667 | |
IMIS-196 | 12090 | 05/10/2018 18:07:46 | 1.033333333 | |
IMIS-196 | 12089 | 05/10/2018 18:07:46 | 1.583333333 | |
IMIS-196 | 12088 | 05/10/2018 13:39:04 | 0.45 | |
IMIS-196 | 12087 | 05/10/2018 11:39:39 | 0.15 | |
IMIS-196 | 12085 | 05/10/2018 11:14:48 | 0.0833333333 | |
IMIS-196 | 12086 | 05/10/2018 11:19:20 | 0.0833333333 | |
IMIS-196 | 12084 | 05/10/2018 10:38:41 | 1.066666667 | |
IMIS-196 | 12083 | 04/10/2018 18:51:39 | 0.8 |
2) History Table containing the status Change
Key | CHANGE_on | Status_Value | Change_effective_from |
IMIS-206 | status | Elab-Review | 08/10/2018 17:06:27 |
IMIS-161 | status | Reviewed | 08/10/2018 16:23:42 |
IMIS-197 | status | review | 08/10/2018 15:04:28 |
TA-3 | status | To do | 08/10/2018 14:39:02 |
TA-2 | status | To do | 08/10/2018 14:38:47 |
TA-1 | status | To do | 08/10/2018 14:38:12 |
IRC-20 | status | Selected | 08/10/2018 14:10:47 |
IMIS-206 | status | Elaboration | 08/10/2018 11:12:44 |
IMIS-206 | status | Screen | 08/10/2018 10:13:10 |
IMIS-206 | status | Open | 08/10/2018 10:12:17 |
IMIS-205 | status | Screen | 08/10/2018 9:33:38 |
IMIS-205 | status | Open | 08/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
Solved! Go to Solution.
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] )
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
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"
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] )
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |