Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I have a table in my dataset 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit' that looks like so:
I want to only pull in those rows for WorkItemID's that had a TagNames that contained the word 'Block' OR where the WorkItemID had a Blocked column that contained 'Yes' (as well as the times it was 'No' for that respective WorkItemID).
Previously when using it for just TagNames I had the following:
WorkItemsBlocked =
FILTER (
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit',
CALCULATE (
COUNT ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames] ),
SEARCH ( "Block", 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames], 1, 0 ) > 0,
ALLEXCEPT ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit', 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId] )
) > 0
)
But I'm not sure how to account for both the TagNames ever containing blocked or the Blocked ever containing Yes.
Any advice?
Solved! Go to Solution.
Hi @FlowViz ,
Thanks for the sample file, please see the calculated table formula below
Sample Table =
var filterTable =
CALCULATETABLE(
VALUES( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId]),
FILTER('WorkItems Blocked (since 1st Jan 20) - Do Not Edit',
SEARCH("block",
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames],1,0)>0
|| lower( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked]) = "yes")
)
return
CALCULATETABLE(
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit', 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId] in filterTable,
lower( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked]) = "no" || lower( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked]) = "yes"
)
creates the following table
Thanks
Proud to be a Super User!
Hi @FlowViz ,
added an additional or condition
WorkItemsBlocked2 =
VAR filterTable =
CALCULATETABLE (
VALUES ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId] ),
FILTER (
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit',
SEARCH (
"Block",
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames],
1,
0
) > 0
|| LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "yes"
)
)
RETURN
CALCULATETABLE (
FILTER (
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit',
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId]
IN filterTable
&&( (
SEARCH (
"Block",
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames],
1,
0
) > 0
|| LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "yes"
)
|| LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "no"
|| LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "")
)
)
Proud to be a Super User!
Amazing! Thank you, this looks to do the trick!
For clarity, the new table should have any work item ID's that ever had a TagNames containing "block" and the associated history (each row is the history of changes to an item) OR any work item ID's that ever had a Blocked value of "Yes"and the associated history from the original table - WorkItems Blocked (since 1st Jan 20) - Do Not Edit
Hi @FlowViz ,
Thanks for the sample file, please see the calculated table formula below
Sample Table =
var filterTable =
CALCULATETABLE(
VALUES( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId]),
FILTER('WorkItems Blocked (since 1st Jan 20) - Do Not Edit',
SEARCH("block",
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames],1,0)>0
|| lower( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked]) = "yes")
)
return
CALCULATETABLE(
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit', 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId] in filterTable,
lower( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked]) = "no" || lower( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked]) = "yes"
)
creates the following table
Thanks
Proud to be a Super User!
Thank you so much!
happy to help
Proud to be a Super User!
I just did another check with another dataset where they only use the tag of "blocked" and do not use blocked "yes" and I get a blank table which is not correct.
I've attached this link to the file/dataset I mention, this should have data as they have items where 'blocked' is used in TagNames...
Hi @FlowViz ,
Sorry for the Delay, here is the updated Table Query, changed the logic in the returned table filter to included ('blocked' or 'yes') or 'no' rows
WorkItemsBlocked2 =
VAR filterTable =
CALCULATETABLE (
VALUES ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId] ),
FILTER (
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit',
SEARCH (
"Blocked",
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames],
1,
0
) > 0
|| LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "yes"
)
)
RETURN
CALCULATETABLE (
FILTER (
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit',
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId]
IN filterTable
&& (
SEARCH (
"Blocked",
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames],
1,
0
) > 0
|| LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "yes"
)
|| LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "no"
)
)
Hope this helps,
Proud to be a Super User!
So that's getting there. It still seems to be missing rows for items that at one point had the 'blocked' TagNames.
Here's a link to the old way/image below of how I'd like it to be:
Here's a link to the new way/image of how it currently looks:
So those 2 occasions where TagNames was blank/not containing Blocked need to be there. Can that be done?
Hi @FlowViz ,
added an additional or condition
WorkItemsBlocked2 =
VAR filterTable =
CALCULATETABLE (
VALUES ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId] ),
FILTER (
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit',
SEARCH (
"Block",
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames],
1,
0
) > 0
|| LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "yes"
)
)
RETURN
CALCULATETABLE (
FILTER (
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit',
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId]
IN filterTable
&&( (
SEARCH (
"Block",
'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames],
1,
0
) > 0
|| LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "yes"
)
|| LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "no"
|| LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "")
)
)
Proud to be a Super User!
One further question - what about an instance where there is no 'Blocked' column in the source table? I've found instances where this can be the case...could the calculated table account for this?
Hi @FlowViz ,
I mocked up some some sample data, so this may not be perfect, but i believe the process is valid
- create a variable with all the workitemids that contain blocked tags and are blocked equals yes
- filter the table by the variable table where blocked = yes or no
Sample Table =
var filterTable = CALCULATETABLE(VALUES('Table'[WorkItemId]), FILTER('Table', SEARCH("blocked", 'Table'[TagName],1,0)>0 && lower('Table'[Blocked]) = "yes"))
return
CALCULATETABLE('Table', 'Table'[WorkItemId] in filterTable, lower('Table'[Blocked]) = "no" || lower('Table'[Blocked]) = "yes" )
Hope this works for you
Proud to be a Super User!
Hi @FlowViz ,
Can you please provide the data as a table, will make things a lot easiser for the community to assist.
Thanks
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |