Requesting help to design a new slicer

[Anonymous]

Hello,

I will put a tl;dr here: in the table below, I am attempting to remove the Iteration Path Slicer as the Iteration Path is dynamic on our project and therefore not reliable, and replace it using static user story tags in ADO to present accurate data for any selected time period.

This is my first position working with Power BI and am stumped with creating a certain visual and am requesting help with a potential solution.  The below visual is a table I inherited that displays our project’s ADO Board User Story data for any particular time period we want to view, and by project work function. 

As I dug into this table I realized that I needed to remove and replace the Iteration Path Slicer as the Iteration Path in ADO is dynamic for our project.  As an example, if a user story was created in the first quarter of 2023 it would have an iteration path of “PI 23.1”.  However, if the user story was not completed in the first quarter and was subsequently moved into second quarter’s work, the owning team member would update the iteration path to “PI 23.2”.  The problem this caused is unreliability in the data presented on the table, for if we wanted to go back and view our working results for PI 23.1 we would want to see that this example user story was part of the Total Stories and part of the “Not Done Stories” in PI 23.1, but this example user story would no longer appear in iteration path PI 23.1 data and would now appear only in iteration path PI 23.2.

A potential solution may be to keep static the iteration path in ADO, but that is a constraint for me as the team has been directed to update the iteration path to the current quarter for user stories that are not completed.  Instead, we are using Tags in ADO to create static criteria to view table results by, and this is where I am requesting help.

We are using the following tagging system:

• BL: PI ##.# to indicate a user story was a baseline story to start a quarter
• HI: PI ##.# to indicate a user story was part of a given quarter, disregarding whether it was a baseline story or not, or its completion status
• If a user story is not completed in the first quarter it was created, it will move to the next quarter and be marked as a baseline story with the BL denoted above

Being new to Power BI and experimenting with a few things, the solution I am currently attempting and not working so far is as follows, with screenshots

• I’ve made a column to check if a given user story is a baseline story in any period

• Since I’m unaware of a way to cram this information down into one column, I have a single column for every quarter to check if a user story is part of that quarter.  Below are screenshots for PI 23.1 and PI 23.2 that check for BL or HI for that respective quarter.

• If a user story has one of those tags for a given quarter, the formula will return PI ##.# or “False” if it does not.
• I am then making and individual slicer for each of these columns, removing the option to select “False” in all of them.  It looks like the screenshot below and this functionality is one item that is working for me so far

• However, the issue I am now running into at this point is that if I select, for example, PI 23.1 when the table updates and I look at the “Baselines” column the information is not correct for the following reason.  The first column I added to my table, “BL Tags Check” does not look for a specific quarter; it looks for any.  So if a user story was created during PI 23.1 but not a baseline user story, and it was also not completed in this quarter and rolled into PI 23.2, it would have a BL: PI23.2 tag and get picked up in the “Baselines” column on the table.  In short, my table would be showing more baseline user stories than intended for the selected time period.

Apologies for the long problem, but if you see any way for me to modify my columns or measures to create a working time period filter for my table I would greatly appreciate it.  If you need access to the dataset I believe I have a cleansed excel spreadsheet I can upload.  Thank you for your time and consideration.

[foodd]

This report is a very effective data story.   The layout and visuals just crush it.  Would you share work in progress copy of the PBIX or PBIT, please?