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) are shown as Clustered Column Chart for the selected Sprint Name. We can't allow users to select multiple sprint names.

The sample PBIX file is shared here for reference. 

 

If the user selects "Sprint 2404" then the chart should show data for Sprint 2404 + Sprint 2403 + Sprint 2402.

If the user selects "Sprint 2403" then the chart should show data for Sprint 2403 + Sprint 2402 + Sprint 2401.

 

Basically 2 (can be configured for 3 or 4 etc, if available) sprints before the selected sprint based on the Start Date/End Date.

 

I was thinking to have a Custom Column used to RANK the rows using RANKX for Sprints based on Start Date/End Date, but not sure how shall I do that.

or I was thinking of another option to have a Dynamic Table that will have One Column - Sprint Name (the selected one) + Previous Sprint +  Previous to Previous Sprint (Based on StartDate/End of the Selected Sprint). And we use this dynamic table in X-Axis and show data from the original Data Set.

These were my thoughts, but not sure if this is possible. Please suggest.

Thanks,

Prabhat

 

Need Help

12 Replies

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