Hi Guys,
Need your help with this dataset I'm working with. I have queried items that contained a tag of 'blocked' and I'm trying to calculate the amount of time lost to being blocked. Dataset like so:
So in this case it would be roughly 3+ days lost when this item had a tag of blocked.
Any tips on how this could be calculated?
Hi @Anonymous ,
Based on your description, you need to create an index column for each [WorkItemId] in power query, here is the whole query code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZMxDoMwDEXvkhnJ2P4JsPYaiAk6gcT9NzK0Vas2+QxVp0rZ3osdWz/jGKzHEFNoQj7Wai8qhjA1p8hl2+f1utwFk47wgXBlDTT9he8I1jLBqoIL45Hw9DlmTlKUGzPB67OB5BCi9dnAfsK7cIrEH5HC5h9EzVGIFeGR8I7V91fhidRLgzwNt7iWeaEzBOQie1je6XQA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkItemId = _t, TagNames = _t, ChangedDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkItemId", Int64.Type}, {"TagNames", type text}, {"ChangedDate", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"WorkItemId"}, {{"Count", each _, type table [WorkItemId=nullable number, TagNames=nullable text, ChangedDate=nullable date]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"TagNames", "ChangedDate", "Index"}, {"Custom.TagNames", "Custom.ChangedDate", "Custom.Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.TagNames", "TagNames"}, {"Custom.ChangedDate", "ChangedDate"}, {"Custom.Index", "Index"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"TagNames", type text}, {"ChangedDate", type date}, {"Index", Int64.Type}})
in
#"Changed Type1"
Close and apply it in power query. Create these two auxiliary calculated columns using dax:
Tag =
VAR next =
CALCULATE (
MAX ( 'Table'[TagNames] ),
FILTER (
ALL ( 'Table' ),
'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
&& 'Table'[Index]
= EARLIER ( 'Table'[Index] ) + 1
)
)
VAR tag =
IF (
'Table'[TagNames] = BLANK (),
2,
IF ( 'Table'[TagNames] = "Blocked" && 'Table'[TagNames] = next, 1, 0 )
)
RETURN
tagPartition =
IF (
'Table'[TagNames] = "Blocked",
RANKX (
FILTER (
ALL ( 'Table' ),
'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
&& 'Table'[Tag] = 0
),
'Table'[ChangedDate],
,
ASC,
DENSE
)
)
Create this measure to get the expected result:
Datediff =
VAR tab =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[TagNames] = "Blocked" ),
'Table'[WorkItemId],
'Table'[ChangedDate],
'Table'[Partition],
"Datediff",
CALCULATE (
DATEDIFF (
CALCULATE (
MIN ( 'Table'[ChangedDate] ),
FILTER (
ALL ( 'Table' ),
'Table'[WorkItemId]
IN DISTINCT ( 'Table'[WorkItemId] )
&& 'Table'[Partition] IN DISTINCT ( 'Table'[Partition] )
)
),
CALCULATE (
MAX ( 'Table'[ChangedDate] ),
FILTER (
ALL ( 'Table' ),
'Table'[WorkItemId]
IN DISTINCT ( 'Table'[WorkItemId] )
&& 'Table'[Partition] IN DISTINCT ( 'Table'[Partition] )
)
),
DAY
),
FILTER (
ALL ( 'Table' ),
'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
&& 'Table'[Partition] = EARLIER ( 'Table'[Partition] )
)
)
)
VAR tb =
SUMMARIZE ( tab, [WorkItemId], [Datediff] )
RETURN
SUMX (
FILTER ( tb, 'Table'[WorkItemId] IN DISTINCT ( 'Table'[WorkItemId] ) ),
[Datediff]
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The approach I took was as follows:
1) Added an index column
2) Added a column:
BlockedItems = IF(SEARCH("Block", WorkItemsBlocked[TagNames],1,0), 1,0)
3) Added another column:
NumberofBlockedDays =
VAR myindex = WorkItemsBlocked[Index]
VAR myworkitemid = WorkItemsBlocked[WorkItemId]
VAR previousindex =
CALCULATE (
MAX ( WorkItemsBlocked[Index] ),
FILTER ( WorkItemsBlocked, WorkItemsBlocked[WorkItemId] = myworkitemid && WorkItemsBlocked[Index] < myindex )
)
VAR previousdate =
CALCULATE (
MAX ( WorkItemsBlocked[ChangedDate] ),
FILTER (
WorkItemsBlocked,
WorkItemsBlocked[Index] = previousindex
&& WorkItemsBlocked[WorkItemId] = myworkitemid
&& WorkItemsBlocked[BlockedItems] = VALUE(1)
)
)
RETURN
IF ( previousdate, WorkItemsBlocked[ChangedDate] - previousdate )
With my results:
@Anonymous
I created a measure, you keep the Title, and this measure on a Table visual. It will show you the days lost:
Day Lost =
var __t =
SELECTCOLUMNS(
ADDCOLUMNS(
FILTER(Table13,Table13[TagNames] = "Blocked"),
"Date1", DATE(YEAR(Table13[ChangedDate]),MONTH(Table13[ChangedDate]),DAY(Table13[ChangedDate]))
),
"Date2",[Date1]
)
return
COUNTROWS(DISTINCT(__t))
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Thanks - that's getting there. Although not quite sure that's correct.
For example if I take ID 284956
Yet in the dataset, this was Blocked from 7th Feb - 6th March (27 days) and 27 March - 24th April (28 days).
So I'm expecting to see 55 days not 14?
Hi @Anonymous ,
Based on your description, you need to create an index column for each [WorkItemId] in power query, here is the whole query code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZMxDoMwDEXvkhnJ2P4JsPYaiAk6gcT9NzK0Vas2+QxVp0rZ3osdWz/jGKzHEFNoQj7Wai8qhjA1p8hl2+f1utwFk47wgXBlDTT9he8I1jLBqoIL45Hw9DlmTlKUGzPB67OB5BCi9dnAfsK7cIrEH5HC5h9EzVGIFeGR8I7V91fhidRLgzwNt7iWeaEzBOQie1je6XQA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkItemId = _t, TagNames = _t, ChangedDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkItemId", Int64.Type}, {"TagNames", type text}, {"ChangedDate", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"WorkItemId"}, {{"Count", each _, type table [WorkItemId=nullable number, TagNames=nullable text, ChangedDate=nullable date]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"TagNames", "ChangedDate", "Index"}, {"Custom.TagNames", "Custom.ChangedDate", "Custom.Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.TagNames", "TagNames"}, {"Custom.ChangedDate", "ChangedDate"}, {"Custom.Index", "Index"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"TagNames", type text}, {"ChangedDate", type date}, {"Index", Int64.Type}})
in
#"Changed Type1"
Close and apply it in power query. Create these two auxiliary calculated columns using dax:
Tag =
VAR next =
CALCULATE (
MAX ( 'Table'[TagNames] ),
FILTER (
ALL ( 'Table' ),
'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
&& 'Table'[Index]
= EARLIER ( 'Table'[Index] ) + 1
)
)
VAR tag =
IF (
'Table'[TagNames] = BLANK (),
2,
IF ( 'Table'[TagNames] = "Blocked" && 'Table'[TagNames] = next, 1, 0 )
)
RETURN
tagPartition =
IF (
'Table'[TagNames] = "Blocked",
RANKX (
FILTER (
ALL ( 'Table' ),
'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
&& 'Table'[Tag] = 0
),
'Table'[ChangedDate],
,
ASC,
DENSE
)
)
Create this measure to get the expected result:
Datediff =
VAR tab =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[TagNames] = "Blocked" ),
'Table'[WorkItemId],
'Table'[ChangedDate],
'Table'[Partition],
"Datediff",
CALCULATE (
DATEDIFF (
CALCULATE (
MIN ( 'Table'[ChangedDate] ),
FILTER (
ALL ( 'Table' ),
'Table'[WorkItemId]
IN DISTINCT ( 'Table'[WorkItemId] )
&& 'Table'[Partition] IN DISTINCT ( 'Table'[Partition] )
)
),
CALCULATE (
MAX ( 'Table'[ChangedDate] ),
FILTER (
ALL ( 'Table' ),
'Table'[WorkItemId]
IN DISTINCT ( 'Table'[WorkItemId] )
&& 'Table'[Partition] IN DISTINCT ( 'Table'[Partition] )
)
),
DAY
),
FILTER (
ALL ( 'Table' ),
'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
&& 'Table'[Partition] = EARLIER ( 'Table'[Partition] )
)
)
)
VAR tb =
SUMMARIZE ( tab, [WorkItemId], [Datediff] )
RETURN
SUMX (
FILTER ( tb, 'Table'[WorkItemId] IN DISTINCT ( 'Table'[WorkItemId] ) ),
[Datediff]
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The approach I took was as follows:
1) Added an index column
2) Added a column:
BlockedItems = IF(SEARCH("Block", WorkItemsBlocked[TagNames],1,0), 1,0)
3) Added another column:
NumberofBlockedDays =
VAR myindex = WorkItemsBlocked[Index]
VAR myworkitemid = WorkItemsBlocked[WorkItemId]
VAR previousindex =
CALCULATE (
MAX ( WorkItemsBlocked[Index] ),
FILTER ( WorkItemsBlocked, WorkItemsBlocked[WorkItemId] = myworkitemid && WorkItemsBlocked[Index] < myindex )
)
VAR previousdate =
CALCULATE (
MAX ( WorkItemsBlocked[ChangedDate] ),
FILTER (
WorkItemsBlocked,
WorkItemsBlocked[Index] = previousindex
&& WorkItemsBlocked[WorkItemId] = myworkitemid
&& WorkItemsBlocked[BlockedItems] = VALUE(1)
)
)
RETURN
IF ( previousdate, WorkItemsBlocked[ChangedDate] - previousdate )
With my results:
Helpful resources
FabCon Global Hackathon
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
