Forum Discussion

prabhatnath's avatar
prabhatnath
Advocate III
3 years ago

Dynamic X Axis based on Slicer using Dynamic table or RANKX

Hi Friends, I have a dataset (Link to the dataset as Google sheet) that has Sprint Name, Start Date, and End Date. The Sprint Name is used as Slicer and Hours Worked (Estimate and Completed columns...
Need Help
johnt75's avatar
johnt75
Super User
3 years ago

Adding an index column to rank the sprints is a good idea, either using the RANKX function as you suggest or you could do it in Power Query by sorting the sprints by date and then adding an index column.

Either way, you could then create a new table like

Sprint Slicer =
SELECTCOLUMNS(
	GENERATE(
		SUMMARIZE( 'Sprint', 'Sprint'[Sprint Name], 'Sprint'[Index]),
		VAR CurrentIndex = 'Sprint'[Index]
		RETURN GENERATESERIES( MAX( CurrentIndex - 2, 1), CurrentIndex)
	),
	"Sprint", 'Sprint'[Sprint Name],
	"Sprint number", [Value]
)

Create a relationship from the new table to your sprint table, so that the new table filters the sprint table, and use the column from the new table in your slicer.