Forum Discussion
Combining filters on multiple fields
Hi everyone, I've been banging my head against this for a while and can't figure out where i am going wrong....
I am trying to create a report that shows work done by a dev squad over an iteration, pulling issue data from our work tracking system (Jira). To this end, i have a history table (history) that shows every status change for each dev item, and this is then summarised (history_sprint_summary) in a separate table that groups these changes into iterations. So far, so good - i was able to put a nice helpful graph to show velocity, story count, etc. Due to how different squads work, I added a filter to allow you to report based on a chosen end status (e.g. how many items went into dev done during an iteration). Now for the painful part...
Our work uses 2 different workflows (1 for stories, 1 for tasks, etc) which do not share the same statuses, and so my report is only counting some of the work that has been done (e.g. the stories) and ignoring the tasks because the don't go through dev done. To try and sidestep this, i have been trying to use CALCULATETABLE to hardcode a filter for the tasks (so that we count all tasks that got closed AND all stories that went to dev done, but with no success.
In simple terms - I want to able to show, for each iteration, all stories that went to the selected status AND all tasks that went to closed.
Things tried so far:
1. Knowing that the status filter would filter history_sprint_summary (my source table), i have been trying to use ALL and REMOVEFILTER to cancel that out - but i get the same result regardless
2. I cancelled the interaction between the drop down filter and the visual (thinking that my filter logic would take over). That stopped any kind of filtering working.
3. Added my filter logic to the DAX that builds up history_sprint_summary (same effect as above).
It's as if the filter logic i am using is being ignored! Below is the DAX i am using to build my history sprint summary table, and my calculated table that should be filtered.
history_sprint_summary = SUMMARIZE (
filter(history, history[sprint_to_date] <> BLANK() && ((history[type] = "Task" ||
history[type] = "Sub-task" ||
history[type] = "Bug Sub-task" ||
history[type] = "Spike") && history[to_status] = "closed") ||
history[to_status] = (sprint_summary[filter])
),
history[issue_key],
history[to_status],
history[story_points],
issues[project_key],
issues[issue_id],
issue_types[type],
history[assignee],"from_date", MIN( history[from_date] ),
"max_sprint_date", min( ( history[sprint_to_date] )))
Sprint_Summary_Calculated =
CALCULATETABLE(history_sprint_summary,
FILTER(ALL(history_sprint_summary),(((history_sprint_summary[type] = "Task" ||
history_sprint_summary[type] = "Sub-task" ||
history_sprint_summary[type] = "Bug Sub-task" ||
history_sprint_summary[type] = "Spike") && history_sprint_summary[to_status] = "closed") ||
history_sprint_summary[to_status] = Sprint_Summary[filter])))
So, after bashing my head against this for the best part of a month, I have finally come up with a somewhat 'cludgy' solution, that at least gives me what i want, and as usual - it was far simpler than i had been making it!
Ultimately - I have created a new column, which combines the status and issue type into a string - i am then using that as a filter for my graphs. It's not pretty, but it is proving functional, and allows me to filter down my fact table to show - for example - all stories at dev done AND all spikes at closed.
8 Replies
Example data would be helpful to see if Status values for the two types of things you are tracking are in the same column (if not, maybe you could concatenate them or write some expression to get values into one column). If so, would a Groups calculated column help you? You could group the equivalent status values into the same values in the new column (e.g., "Open", "Closed", "In Progress"), which would allow you write much simpler DAX expressions.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Below is the raw history
issue_key from_date to_date from_status to_status story_points sprint_to_date type PAY-298 20/04/2020 11:04 20/04/2020 14:24 new ready for dev 5 20 April 2020 Story PAY-297 17/04/2020 09:42 20/04/2020 14:22 new ready for dev 8 20 April 2020 Story PAY-299 20/04/2020 12:02 20/04/2020 14:27 new ready for dev 8 20 April 2020 Story PAY-291 09/04/2020 12:55 20/04/2020 10:00 new ready for dev 5 20 April 2020 Story PAY-295 16/04/2020 07:10 20/04/2020 09:59 new ready for dev 5 20 April 2020 Story PAY-296 16/04/2020 07:21 20/04/2020 09:59 new ready for dev 8 20 April 2020 Story PAY-288 09/04/2020 11:18 15/04/2020 12:49 new ready for dev 5 20 April 2020 Story PAY-285 06/04/2020 12:06 08/04/2020 07:11 new ready for dev 8 20 April 2020 Story PAY-290 09/04/2020 12:42 16/04/2020 09:43 new ready for dev 5 20 April 2020 Story PAY-286 07/04/2020 11:51 08/04/2020 08:40 new ready for dev 5 20 April 2020 Story PAY-289 09/04/2020 12:28 16/04/2020 09:46 new ready for dev 5 20 April 2020 Story PAY-293 10/04/2020 09:34 10/04/2020 09:37 new ready for dev 0 20 April 2020 Bug PAY-292 10/04/2020 09:07 10/04/2020 09:11 new ready for dev 0 20 April 2020 Bug PAY-269 12/03/2020 06:44 15/04/2020 10:18 in progress closed 5 20 April 2020 Spike PAY-288 15/04/2020 12:54 17/04/2020 03:26 in dev peer review 5 20 April 2020 Story PAY-284 07/04/2020 09:55 16/04/2020 05:21 in dev peer review 8 20 April 2020 Story PAY-281 02/04/2020 09:54 08/04/2020 08:47 in dev peer review 5 20 April 2020 Story PAY-286 08/04/2020 08:40 14/04/2020 15:36 in dev peer review 5 20 April 2020 Story PAY-293 13/04/2020 09:51 14/04/2020 09:58 in dev peer review 0 20 April 2020 Bug PAY-266 30/03/2020 13:25 09/04/2020 09:11 qa release test/uat 3 20 April 2020 Story PAY-277 10/04/2020 09:09 10/04/2020 12:09 qa release test/uat 5 20 April 2020 Story PAY-286 16/04/2020 16:31 20/04/2020 09:39 qa release test/uat 5 20 April 2020 Story PAY-293 15/04/2020 09:51 15/04/2020 14:48 qa release test/uat 0 20 April 2020 Bug PAY-270 30/03/2020 13:25 10/04/2020 12:41 qa release test/uat 3 20 April 2020 Story PAY-283 02/04/2020 16:34 09/04/2020 10:03 ready for dev in dev 8 20 April 2020 Story PAY-288 15/04/2020 12:49 15/04/2020 12:54 ready for dev in dev 5 20 April 2020 Story PAY-285 08/04/2020 07:11 09/04/2020 03:22 ready for dev in dev 8 20 April 2020 Story PAY-284 02/04/2020 16:35 07/04/2020 09:55 ready for dev in dev 8 20 April 2020 Story PAY-289 16/04/2020 09:46 17/04/2020 08:51 ready for dev in dev 5 20 April 2020 Story PAY-293 10/04/2020 09:37 13/04/2020 09:51 ready for dev in dev 0 20 April 2020 Bug PAY-292 10/04/2020 09:11 10/04/2020 10:46 ready for dev in dev 0 20 April 2020 Bug PAY-277 09/04/2020 09:22 10/04/2020 09:09 ready for qa qa 5 20 April 2020 Story PAY-281 13/04/2020 06:33 14/04/2020 14:30 ready for qa qa 5 20 April 2020 Story PAY-286 16/04/2020 08:38 16/04/2020 16:31 ready for qa qa 5 20 April 2020 Story PAY-293 15/04/2020 07:13 15/04/2020 09:51 ready for qa qa 0 20 April 2020 Bug PAY-274 08/04/2020 07:43 09/04/2020 09:28 ready for qa qa 5 20 April 2020 Story PAY-276 07/04/2020 11:47 08/04/2020 07:25 ready for qa qa 13 20 April 2020 Story PAY-292 10/04/2020 10:47 10/04/2020 11:00 ready for qa qa 0 20 April 2020 Bug PAY-277 10/04/2020 12:09 14/04/2020 07:10 release test/uat dev done 5 20 April 2020 Story PAY-286 20/04/2020 09:39 20/04/2020 17:12 release test/uat dev done 5 20 April 2020 Story PAY-266 09/04/2020 09:11 10/04/2020 11:19 release test/uat dev done 3 20 April 2020 Story PAY-270 10/04/2020 12:41 13/04/2020 07:22 release test/uat dev done 3 20 April 2020 Story PAY-274 09/04/2020 09:28 10/04/2020 11:24 release test/uat dev done 5 20 April 2020 Story PAY-277 06/04/2020 08:36 09/04/2020 09:22 peer review ready for qa 5 20 April 2020 Story PAY-281 08/04/2020 08:47 13/04/2020 06:33 peer review ready for qa 5 20 April 2020 Story PAY-286 14/04/2020 15:36 16/04/2020 08:38 peer review ready for qa 5 20 April 2020 Story PAY-293 14/04/2020 09:58 15/04/2020 07:13 peer review ready for qa 0 20 April 2020 Bug PAY-274 31/03/2020 09:27 08/04/2020 07:43 peer review ready for qa 5 20 April 2020 Story PAY-276 03/04/2020 07:43 07/04/2020 11:47 peer review ready for qa 13 20 April 2020 Story PAY-286 08/04/2020 08:40 08/04/2020 08:40 ready for dev in dev 5 20 April 2020 Story PAY-292 10/04/2020 11:00 10/04/2020 11:00 release test/uat closed 0 20 April 2020 Bug PAY-276 08/04/2020 07:25 08/04/2020 07:25 release test/uat dev done 13 20 April 2020 Story PAY-292 10/04/2020 10:47 10/04/2020 10:47 peer review ready for qa 0 20 April 2020 Bug PAY-292 10/04/2020 10:46 10/04/2020 10:47 in dev peer review 0 20 April 2020 Bug PAY-281 14/04/2020 14:30 14/04/2020 14:30 qa release test/uat 5 20 April 2020 Story PAY-274 09/04/2020 09:28 09/04/2020 09:28 qa release test/uat 5 20 April 2020 Story PAY-276 08/04/2020 07:25 08/04/2020 07:25 qa release test/uat 13 20 April 2020 Story PAY-292 10/04/2020 11:00 10/04/2020 11:00 qa release test/uat 0 20 April 2020 Bugfrom that we filter down to the summary table (this records the first instance that a story goes to a given status within each iteration - sometimes things move back out of qa for example):
max_sprint_date from_date squad_sprint issue_key to_status story_points project_key issue_id type 20/04/2020 23:59 12/03/2020 06:44 PAY-20/04/2020 23:59:59 PAY-269 closed 5 PAY 236505 Spike 20/04/2020 23:59 10/04/2020 12:09 PAY-20/04/2020 23:59:59 PAY-277 dev done 5 PAY 237756 Story 20/04/2020 23:59 20/04/2020 09:39 PAY-20/04/2020 23:59:59 PAY-286 dev done 5 PAY 238428 Story 20/04/2020 23:59 09/04/2020 09:28 PAY-20/04/2020 23:59:59 PAY-274 dev done 5 PAY 237124 Story 20/04/2020 23:59 20/04/2020 11:04 PAY-20/04/2020 23:59:59 PAY-298 ready for dev 5 PAY 239005 Story 20/04/2020 23:59 09/04/2020 12:55 PAY-20/04/2020 23:59:59 PAY-291 ready for dev 5 PAY 238560 Story 20/04/2020 23:59 16/04/2020 07:10 PAY-20/04/2020 23:59:59 PAY-295 ready for dev 5 PAY 238745 Story 20/04/2020 23:59 09/04/2020 12:42 PAY-20/04/2020 23:59:59 PAY-290 ready for dev 5 PAY 238557 Story 20/04/2020 23:59 16/04/2020 09:46 PAY-20/04/2020 23:59:59 PAY-289 in dev 5 PAY 238556 Story 20/04/2020 23:59 14/04/2020 14:30 PAY-20/04/2020 23:59:59 PAY-281 release test/uat 5 PAY 237999 Story 20/04/2020 23:59 17/04/2020 09:42 PAY-20/04/2020 23:59:59 PAY-297 ready for dev 8 PAY 238862 Story 20/04/2020 23:59 20/04/2020 12:02 PAY-20/04/2020 23:59:59 PAY-299 ready for dev 8 PAY 239012 Story 20/04/2020 23:59 16/04/2020 07:21 PAY-20/04/2020 23:59:59 PAY-296 ready for dev 8 PAY 238746 Story 20/04/2020 23:59 09/04/2020 09:11 PAY-20/04/2020 23:59:59 PAY-266 dev done 3 PAY 235869 Story 20/04/2020 23:59 10/04/2020 12:41 PAY-20/04/2020 23:59:59 PAY-270 dev done 3 PAY 236744 Story 20/04/2020 23:59 08/04/2020 07:25 PAY-20/04/2020 23:59:59 PAY-276 dev done 13 PAY 237753 Story 20/04/2020 23:59 15/04/2020 09:51 PAY-20/04/2020 23:59:59 PAY-293 release test/uat 0 PAY 238572 Bug 20/04/2020 23:59 10/04/2020 11:00 PAY-20/04/2020 23:59:59 PAY-292 closed 0 PAY 238571 Bugi'm not entirely sure that grouping will work, as both workflows have the Closed status - so it would not be sufficient for me to say show me everything that moved to closed.
Thank you for providing example data. I will continue to help work through this with you if you want. Here are images of what I was talking about to try to simplify and harmonize the two workflows. I know this is oversimplified but just trying to better understand your goal. Can you provide an example output with the rights answers so I have something to shoot for with a potential new DAX expression? Are you looking for a new DAX table? Measure? Visual with measure?
So, after bashing my head against this for the best part of a month, I have finally come up with a somewhat 'cludgy' solution, that at least gives me what i want, and as usual - it was far simpler than i had been making it!
Ultimately - I have created a new column, which combines the status and issue type into a string - i am then using that as a filter for my graphs. It's not pretty, but it is proving functional, and allows me to filter down my fact table to show - for example - all stories at dev done AND all spikes at closed.
