Forum Discussion
Text Slicer filter
I would want to implement a different sort of a "From-To" slicer using texts instead of dates and was wondering if this is possible.
Here is the scenario:
Working in an agile environment, I have a table with a sprint start date and end date together with the sprint iteration. To illustrate, let's say we have a one-week sprint cycle as shown on the table below:
Is it possible to create a "From-To" slicer using the "Iteration" column instead of the dates, like below?
Please note that the "Iteration" field can be named anything so we can't sort using the suffix on that column. With that being the case, how do I filter the visuals using the "Start Date" field instead of the "Iteration" field?
A bonus would be to have the contents of second dropdown filtered by the first dropdown selection. 🙂
By the way, the reasoning behind why I would want to implemet this is it is more intuitive to select by Iteration instead of trying to remember which dates those Iteration belong to.
Thanks in advance.
I was able to find a way to resolve my issue. Not sure if this is the best way to do it but it works for me. 🙂
I want to share what I did in case someone is in the same situation.I created a duplicate of my sprint tables and made them disconnected as amit suggested (for discussions sake, let's name them "FromTable" and "ToTable" tables). Then I created a measure on the "ToTable" table similar to the one below:
From_Start_Flag = IF(VALUES(ToTable[Start Date]) >= SELECTEDVALUE(FromTable[Start Date]), 1, 0)
I then used the From_Start_Flag measure as a filter on the ToTable so that it will exclude dates earlier than the FromTable.
I also created a similar measure on the visual's table to filter out by Start Date as follows:
SprintRange_Flag =
IF(VALUES(UserStories[Start Date]) >= SELECTEDVALUE(FromTable[Start Date]) && VALUES(UserStories[Start Date]) <= SELECTEDVALUE(ToTable[Start Date]), 1, 0)I then added that measure as a filter on the visual.
There you go. I hope this helps someone.
4 Replies
Anonymous , first of all add a column in table that will order to the sprint
Then have two independent/disconnected tables with Iteration name and Iteration order and measure like
Date Range Using 2 slicer =
var _max = minx(allselected(Iteration1), Iteration1[Iteration order])
var _min = maxx(allselected(Iteration2), Iteration2[Iteration order])
return
calculate(countrows(Table), Filter(Table,Table[Iteration order] <=_max && Table[Iteration order] >=_min) )Power BI How to use/compare two Date/Period slicers: https://youtu.be/WSeZr_-MiTg
Thank you amitchandak, I will go through the video and see if it solves my scenario. One more thing, how do I limit the second dropdown so it does not contain the sprints earlier than what was selected in the first dropdown? Thanks again!
Thanks again amitchandak. I am new to PowerBI so I am unable to make your example work on my end. Do you have a more detailed example? Thanks!
I was able to find a way to resolve my issue. Not sure if this is the best way to do it but it works for me. 🙂
I want to share what I did in case someone is in the same situation.I created a duplicate of my sprint tables and made them disconnected as amit suggested (for discussions sake, let's name them "FromTable" and "ToTable" tables). Then I created a measure on the "ToTable" table similar to the one below:
From_Start_Flag = IF(VALUES(ToTable[Start Date]) >= SELECTEDVALUE(FromTable[Start Date]), 1, 0)
I then used the From_Start_Flag measure as a filter on the ToTable so that it will exclude dates earlier than the FromTable.
I also created a similar measure on the visual's table to filter out by Start Date as follows:
SprintRange_Flag =
IF(VALUES(UserStories[Start Date]) >= SELECTEDVALUE(FromTable[Start Date]) && VALUES(UserStories[Start Date]) <= SELECTEDVALUE(ToTable[Start Date]), 1, 0)I then added that measure as a filter on the visual.
There you go. I hope this helps someone.
