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

 

 

johnt75's avatar
johnt75
Super User
2 years ago

You need a relationship from the new table to the existing one. You should use the column from the new table in the slicer and the columns from the existing table in the other visuals

  • prabhatnath's avatar
    prabhatnath
    Advocate III
    2 years ago

    I forgot to have the relationship set up. I Tried but got the below error while setting the relation. Similar error for any Cardinality I select. I am not an expert so might be making mistakes.

     

     

     

    Also, I was thinking 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.
    and then setup the relationship. This will ensure the Dynamic table has updated Sprint values based on Slicer Selection and can be used as X Axis.

    Thanks,

    Prabhat

    • johnt75's avatar
      johnt75
      Super User
      2 years ago

      The message you got when trying to create the relationship isn't an error, its a warning. Many-to-many relationships are normally not the best, hence the warning, but its OK in this situation. Change the cross filter direction to single so that the new table filters the sprint table.

      I don't think we need to add anything else to the new table. Its only purpose is to be used in the slicer so that it filters the main sprint table to just the selected sprints. You can use the sprint name column from the main sprint table in all your visuals other than the slicer.

      • prabhatnath's avatar
        prabhatnath
        Advocate III
        2 years ago

        Thanks, John for the clarification/help.

        I tried the same but seems the chart does not show 2 previous sprint details of what is selected in the Slicer. Here I have uploaded the updated PBIX for your review.  It will be a great help if you can quickly review and suggest where I am doing wrong.

         

        Thanks,

        Prabhat