Burndown with visual level filter

[aslee]

Hi All,

I am trying to create a burndown of remaining tasks left, based on the task end date, which falls into an Agile sprint number. Each task is also assigned to a team. Sample data below:

Team	Sprint	Key
1	120	ABC-1
2	125	ABC-2
3	122	ABC-3
4	120	ABC-4
2	121	ABC-5
3	123	ABC-6
4	127	ABC-7
1	129	ABC-8
2	124	ABC-9
3	126	ABC-10
2	122	ABC-11
1	120	ABC-12
4	125	ABC-13
4	126	ABC-14
4	127	ABC-15
2	128	ABC-16
1	123	ABC-17
3	122	ABC-18
2	124	ABC-19
1	125	ABC-20

This is the best I have come up with:

burndown = CALCULATE(COUNT(tasks[Key]), FILTER(ALL(tasks), tasks[sprint]>=MIN(tasks[sprint])))

Here I encounter two problems:

1.  It does not take into account visual filters, so I can't easily filter by team, unless I include the specific team in the calculated measure. I would rather just have 1 measure rather than create 4. 
2. There may be sprints where there are no tasks due in that particular iteration, so the column for that sprint shows as empty. I want it to still show a sum of remaining tasks left regardless.

Here is what happens when there is no filter of team - works fine

Here is what happens when I change the visual filter to team 1. Shows as 20 tasks for team 1, and missing columns of data

And here is what I would ideally like it to look like, as an example for Team 1

Any assistance would be appreciated!

[Phil_Seamark · Accepted solution]

Hi @aslee

This small tweak might help.  And if you want a bar chart for each sprint, you could have a separate table for that.  Let me know if it looks close.

burndown = 
	CALCULATE(
		COUNT(tasks[Key]), 
		FILTER(
			ALLEXCEPT('Tasks','Tasks'[Team]), 
			tasks[sprint]>=MIN(tasks[sprint])
			)
		)

[aslee]

Thanks @Phil_Seamark - that definitely looks better! 

Could you clarify what you mean by a separate table? 

[Phil_Seamark]

Hi @aslee,

Just thinking the chart you get with my measure doesn't match perfectly your desired chart.  I can have a look at this a little later tonight to flesh out a measure that means the chart looks closer to your desired result.

[aslee]

Relatively new to DAX but managed to come up with this solution for anyone else who might need it:

Created a new table 'Join' where I manually entered our sprint numbers into one column.

Then created a calculated column 

Burndown = COUNTX(
           FILTER(tasks, 
           tasks[sprint]>='Join'[sprint]), tasks[key])

This essentially fills in the gaps for sprints where no tasks are due. 

I also created 4 additional columns with filters for each team, which makes it easy to use in the column charts. 

Eg

Team 1 = COUNTX(
           FILTER(tasks, 
           tasks[sprint]>='Join'[sprint]&&tasks[team]="1"), tasks[key])

Thanks @Phil_Seamark for your push in the right direction!