Combining filters on multiple fields

[chipchidster]

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])))

[chipchidster · Accepted solution]

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.

[mahoneypat]

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

[chipchidster]

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	Bug

from 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	Bug

i'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.  

[mahoneypat]

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?

[mahoneypat]

Please tell me if this measure is getting warmer.  I'm still trying to understand your data.  Please let me know which date you use on your x-axis.  The sample data only had one row for each FromDate, so it is hard to do much.  I just did a simple countrows but the same approach with the filter clauses in the calculates can be used with any measure.

Count Selected and Tasks Closed =
VAR selectedstatuscountexcepttasks =
CALCULATE ( COUNTROWS ( Summary ), Summary[type] <> "Task" )
VAR tasksclosedcount =
CALCULATE (
COUNTROWS ( Summary ),
ALL ( Summary[to_status] ),
Summary[type] = "Task",
Summary[to_status] = "Closed"
)
RETURN
selectedstatuscountexcepttasks + tasksclosedcount

Please let me know if this is any closer.

Regards,

Pat

[chipchidster]

Hi Pat, thanks for that - I will give it a try in the morning and let you know how it goes. I assume by using a calculated field like that, I lose the ability to let the user view the records that make up the figure from the visualisation?

I'm using max_sprint_date as the X axis. The lines are built using fields from the issues table (which holds the issue metadata) that table links to the history and history_sprint_summary via the issue_key field.

[Anonymous]

Hi @chipchidster.

Please change your data model to facilitate flexible calculations. You've got 2 different entities that you should not mix: Stories and Tasks. These need 2 different fact tables because they are very different. Do not try to sqeeze a round peg into a square hole (or the other way round).

You need a Date table, 2 fact tables as above and some dimensions. Some of them will be shared, some of them will not. You have to figure out which of them will be which and this depends on what you're trying to achive.

DAX is simple and fast on CORRECT models ONLY. Otherwise, you'll be creating some monster formulas that nobody else will understand... including you after 2 months. What's more, you'll never know if they return correct figures under all circumstances.

Please stay away from bad data modes as they'll bite you when you least expect it. Follow the good and time-honored rules of dimensional design and you'll save yourself a lot of grief and trouble.

Best
D

[chipchidster]

Short version:

So i am pretty agnostic about "how i get there" but in terms of the solution, i had been trying to create view of the history_sprint_summary table that is filtered down to the selected status (from my status slicer), and any tasks that have moved to closed for each iteration period.  Ultimately i want to produce a graph like the one below, that shows work done per iteration, and that the user can drill down to see the items.

Longer version:

I am trying to produce a graph which will show me a trendline of Story Point, Story Count and Cycle Time over a number of iterations.  Currently this works fine by using the history_sprint_summary table, but as i said, i am then allowing the user to filter on end status (e.g. dev done - it could easily be Ready for Live, or any other status).  I would also like the user to be able to right click the data points to be able to see what items go to make it up.

So far so good. 

Stories have a workflow of "Backlog, Ready for Dev, In Dev, Peer Review, Ready for QA, QA, Release Test/UAT, Ready for Live, Closed

Tasks have  a workflow of To Do, In Progress, Closed

As you can see, the problem with that is that the tasks never go through Dev Done (the usual dev reporting point) or many of the other workflow statuses.  As i said above - I want to have the graph above that shows work done per iteration, allowing the user to select the workflow status they report at (e.g. Dev Done), but also, i want to include for every data point, all tasks that moved to closed in the same period.  I can't show everything that moved to closed - as the story workflow also uses the closed status.