Forum Discussion

prabhatnath's avatar
prabhatnath
Advocate III
2 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
2 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.

  • prabhatnath's avatar
    prabhatnath
    Advocate III
    2 years ago

    Thank you for your help on the Dynamic table.
    As I am new I need guidance on using the RANKX function to add the index column.

    Reg. Power Query to sort the sprints by Date: 
    There are multiple rows (all work items) with the same sprint start date and Sprint Name as they all belong to the same Sprint, so adding an Index after sorting Start Date or Start Date + SprintName will not work. So the Index/Rank should rank all Work items/rows belonging to a Single Sprint with one number .

    So the data should have Index like below:

    Thanks,

    Prabhat

     

     

    • johnt75's avatar
      johnt75
      Super User
      2 years ago

      Try

      Sprint index =
RANKX (
    ALL ( 'Table'[Sprint], 'Table'[Start date] ),
    'Table'[Start date],
    ,
    ASC
)
      • prabhatnath's avatar
        prabhatnath
        Advocate III
        2 years ago

        Thank you for the RANKX and it is doing exactly what I was looking for.
        Once this Index is added I tried your virtual table code as below but unfortunately, the table value is not getting updated based on the Sprint Slicer selection. Below is the Screenshot for your reference.

         

        If the user selects "Sprint 2404" then the chart should show data for Sprint 2404 + Sprint 2403 + Sprint 2402. This means the new table should have Sprint Values: 2404, 2403, and 2402.

         

        If the user selects "Sprint 2403" then the chart should show data for Sprint 2403 + Sprint 2402 + Sprint 2401. And here the new table should have Sprint Values: 2403, 2402 and 2401.


        I think we should fill the new Dynamic Table with:
        Sprint Name that was selected by the user in the Slicer +
        Append Sprint Name from the Sprint table that has Index - 1 of the Selected Sprint Index.
        Append Sprint Name from the Sprint table that has Index - 2 of the Selected Sprint Index.
        This is just my thought but not sure of that is possible. This will ensure the Dynamic table has updated Sprint values based on Slicer Selection and can be used as X Axis.

        Thanks,

        Prabhat

         

         

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