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

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    You have the relationship on the wrong columns. You need it from Sprint Index to Sprint number.

  • prabhatnath's avatar
    prabhatnath
    Advocate III
    3 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  

  • prabhatnath's avatar
    prabhatnath
    Advocate III
    3 years ago

    Hi John,
    Thank you for the hint on the relation column it worked as expected for the Chart, where the chart shows the previous 2 sprint data based on the relation, but Unfortunately, I realized that other parts of the report do not work as expected.

     

    Reason:

    Rest all visuals in the report depending on the main table or the selected Sprint Name, but as the main table gets filtered with "current sprint plus previous 2 sprints" all other visuals are impacted.

    Ideally the "Column Chart" should be shown based on the "Selected Sprint and the previous 2 sprints" but rest of all visuals should depend on the "main table or only with the Selected Sprint". 

     

    Please suggest.

    Thanks,

    Prabhat 

  • prabhatnath's avatar
    prabhatnath
    Advocate III
    2 years ago

    Hi,  johnt75 or any other experts here. Please guide me in this.
    Thanks,

    Prabhat

  • prabhatnath's avatar
    prabhatnath
    Advocate III
    2 years ago

    Hi Friends,
    I have uploaded an updated PBI file here and looking for any help.
    You may go ahead and discard all logic and suggest any new way to get that.

     

    Basically, I have data that has Sprints column that is used in a slicer (single select only) and many visuals in the report are based on the Sprint that is selected.

     

    But I have a requirement where the Column Chart should show data for the selected Sprint and the Previous 2 Sprints. The previous 2 sprints can be determined based on the Start Data and End Date.

    Please suggest.
    Thanks,
    Prabhat Nath