The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi everyone,
I have a very anoying subject with a difficult request.
I have 2 tables :
- the list of the deployments by product with deployment_id, product_id, deployment_date
- the list of incidents by product with product_id, incident_date, time_of_resolution
The final objective is to calculate the average time of resolution of tickets by product, but also generated by some selected deployments.
Deployments
id | product_id | deployment_datetime |
1 | toto | 01/01/2024 00:00:00 |
2 | tata | 01/01/2024 00:00:00 |
3 | tata | 02/02/2024 00:00:00 |
4 | titi | 01/01/2024 00:00:00 |
5 | titi | 02/02/2024 00:00:00 |
6 | titi | 03/03/2024 00:00:00 |
Incidents
product_id | incident_datetime | resolution_time |
toto | 01/01/2024 02:00:00 | 46 |
tata | 01/01/2024 02:00:00 | 62 |
titi | 02/02/2024 02:00:00 | 108 |
titi | 12/12/2023 02:00:00 | 2 |
The difficulty is how link this two tables ?
I would like to add in the incidents table a column "linked_deployment_id" with this definition :
An incident have to be linked to the last incident (closest previous deployment date), for the same product.
final_incidents :
product_id | incident_datetime | resolution_time | linked_deployment_id |
toto | 01/01/2024 02:00:00 | 46 | 1 |
tata | 01/01/2024 02:00:00 | 62 | 2 |
titi | 02/02/2024 02:00:00 | 108 | 5 |
titi | 12/12/2023 02:00:00 | 2 | null |
Have you a solution to do it in power query or DAX ?
Thank's a lot !
Solved! Go to Solution.
You can merge the tables on the product ID column.
Then extract the deployment_id according to your rules.
In the code below Incidents and Deployments refer to your two source tables.
let
Source = Table.NestedJoin(Incidents, {"product_id"}, Deployments, {"product_id"}, "Deployments", JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(Source, "linked_deployment_id", each let
//Incidents only after Deployment Date
a = Table.SelectRows([Deployments], (r)=>[incident_datetime]>=r[deployment_datetime]),
//return list of differences to determine position of the minimum difference
b = List.Accumulate(
a[deployment_datetime],
{},
(s,c)=> s & {Duration.TotalSeconds([incident_datetime] - c)}),
//return the appropriate ID
id = try a[id]{List.PositionOf(b,List.Min(b))} otherwise null
in
id, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Deployments"})
in
#"Removed Columns"
Hi @MichiyoTora, different approach:
Result
let
Deployments = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSrJL8kHUgaG+kBkZGBkomBgYAVGSrE60UpGICWJJYl4lBgjKTHSByJMJSYgJZklmXhMMUVSgsMUMyQlxvpAhKYkFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, product_id = _t, deployment_datetime = _t]),
Incidents = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKskvyVfSUTIw1AciIwMjEwUDIysDAyACipqYKcXqANUkliTiVGNmBFGTWZIJUmOkD0ToagwNLJAVGRrpG4IVGSMrApoTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [product_id = _t, incident_datetime = _t, resolution_time = _t]),
ChangedTypeDeployments = Table.TransformColumnTypes(Deployments,{{"id", Int64.Type}, {"deployment_datetime", type datetime}}),
ChangedTypeIncidents = Table.TransformColumnTypes(Incidents,{{"incident_datetime", type datetime}, {"resolution_time", type number}}),
MergedQueries = Table.NestedJoin(ChangedTypeIncidents, {"product_id"}, ChangedTypeDeployments, {"product_id"}, "Deployments", JoinKind.LeftOuter),
Ad_LinkedDeploymentId = Table.AddColumn(MergedQueries, "linked_deployment_id", each Table.Last(Table.SelectRows([Deployments], (x)=> x[deployment_datetime] <= [incident_datetime]))[id]?, Int64.Type),
RemovedColumns = Table.RemoveColumns(Ad_LinkedDeploymentId,{"Deployments"})
in
RemovedColumns
I'm sorry, I was vacationning without easy access to the web. I tried the two previous solutions and it works with a small dataset but I have millions deployments and incidents, and it is too big to work with that, it charges infinitely. I think that working in power query isn't the best solution in my case because it's too greddy. I'll try to insert the "foreign key" in incidents table in my data source directly (materialized views).
If someone have a best idea to optimize my problem, I remain open to other suggestions.
Thank's for your time and your help !
Hi @MichiyoTora ,
I noticed that two super users have provided possible solutions to your issue. Could you please let us know if any of these solutions have resolved your problem? If so, kindly accept the helpful response as the solution.
Thank you!
Best Regards,
Stephen Tao
Hi @MichiyoTora, different approach:
Result
let
Deployments = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSrJL8kHUgaG+kBkZGBkomBgYAVGSrE60UpGICWJJYl4lBgjKTHSByJMJSYgJZklmXhMMUVSgsMUMyQlxvpAhKYkFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, product_id = _t, deployment_datetime = _t]),
Incidents = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKskvyVfSUTIw1AciIwMjEwUDIysDAyACipqYKcXqANUkliTiVGNmBFGTWZIJUmOkD0ToagwNLJAVGRrpG4IVGSMrApoTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [product_id = _t, incident_datetime = _t, resolution_time = _t]),
ChangedTypeDeployments = Table.TransformColumnTypes(Deployments,{{"id", Int64.Type}, {"deployment_datetime", type datetime}}),
ChangedTypeIncidents = Table.TransformColumnTypes(Incidents,{{"incident_datetime", type datetime}, {"resolution_time", type number}}),
MergedQueries = Table.NestedJoin(ChangedTypeIncidents, {"product_id"}, ChangedTypeDeployments, {"product_id"}, "Deployments", JoinKind.LeftOuter),
Ad_LinkedDeploymentId = Table.AddColumn(MergedQueries, "linked_deployment_id", each Table.Last(Table.SelectRows([Deployments], (x)=> x[deployment_datetime] <= [incident_datetime]))[id]?, Int64.Type),
RemovedColumns = Table.RemoveColumns(Ad_LinkedDeploymentId,{"Deployments"})
in
RemovedColumns
You can merge the tables on the product ID column.
Then extract the deployment_id according to your rules.
In the code below Incidents and Deployments refer to your two source tables.
let
Source = Table.NestedJoin(Incidents, {"product_id"}, Deployments, {"product_id"}, "Deployments", JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(Source, "linked_deployment_id", each let
//Incidents only after Deployment Date
a = Table.SelectRows([Deployments], (r)=>[incident_datetime]>=r[deployment_datetime]),
//return list of differences to determine position of the minimum difference
b = List.Accumulate(
a[deployment_datetime],
{},
(s,c)=> s & {Duration.TotalSeconds([incident_datetime] - c)}),
//return the appropriate ID
id = try a[id]{List.PositionOf(b,List.Min(b))} otherwise null
in
id, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Deployments"})
in
#"Removed Columns"
I'll try this tomorrow, thank's a lot for the idea, I hope I'll success to apply to my use case because I simplified the problem a lot to post it here. I'll come back tu you if it works ... or not 😃
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.