Creating a Start-to-End Filter for Projects in Power BI

[DamianStudent]

Hello!

I'm facing a challenge that I need some help with. I'm working on a report that includes two main tables: Student and Projects. The Student table contains columns for Student Name, Date, Project, and Rate. The Projects table lists projects.

My goal is to create a "start-to-end" (from-to) filter that allows a user to select a starting and an ending project (e.g., from Project Beta to Project Eta), and then display results for all projects within that range. Importantly, the filtration should be based on the project names, following their sequence in the Projects table, rather than on indices or other criteria.

My question is: how can I set up such a filter that respects the chronological order of projects defined in the Projects table and allows for dynamic filtering of data between selected projects?

Example with indexes, but it isn't the goal.
Starting table:

Index modification:

I've been searching everywhere for a solution, including forums, YouTube, etc., but I couldn't find anything on this topic beyond filtering using dates or numbers, which is not suitable for my task.

Thank you in advance for any help and suggestions.

[v-cgao-msft · Accepted solution]

Hi @DamianStudent ,

Values of type Text do not apply to the between slicer.
Here is the workaround:
Copy a project table. At this point we have two project tables.

Rename both tables and do not create a relationship with the student table.

Merge the [Index] column into the student table.

create a new measure:

Measure = 
VAR _start_project_index = MIN('start project'[Index])
VAR _end_project_index = MAX('end project'[Index])
VAR _cur_project_index = SELECTEDVALUE('students'[Index])
VAR _filter = IF(_cur_project_index>=_start_project_index && _cur_project_index<=_end_project_index,1)
RETURN
_filter

And apply it to the visual's filter.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[v-cgao-msft · Accepted solution]

Hi @DamianStudent ,

Values of type Text do not apply to the between slicer.
Here is the workaround:
Copy a project table. At this point we have two project tables.

Rename both tables and do not create a relationship with the student table.

Merge the [Index] column into the student table.

create a new measure:

Measure = 
VAR _start_project_index = MIN('start project'[Index])
VAR _end_project_index = MAX('end project'[Index])
VAR _cur_project_index = SELECTEDVALUE('students'[Index])
VAR _filter = IF(_cur_project_index>=_start_project_index && _cur_project_index<=_end_project_index,1)
RETURN
_filter

And apply it to the visual's filter.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[DamianStudent · Accepted solution]

Thank you, it works great 🙂

[vanessafvg]

can you please demonstrate with an example what your expected output is based on your requirements, it impossible to test a solution without having an example to map to.

so please show what it is you would like to have and what you expect it to output.

[DamianStudent]

Hello,
I would like to achive something like this:

I enter the starting and ending project into the range, instead of indices.
It doesn't have to be a slider; the values can be selected in a different way, for example, through a dropdown list.

[vanessafvg]

are you able to provide the in text form?

[DamianStudent]

Do you mean text form of tables?
I can share CSVs.
student table:
Student Name,Date,Project,Rate
John D,2023-01-10,Project Alpha,Excellent
Alice M,2023-01-12,Project Beta,Good
Michael B,2023-01-15,Project Beta,Satisfactory
Emily W,2023-01-20,Project Beta,Excellent
David K,2023-01-25,Project Beta,Good
Sarah T,2023-01-30,Project Gamma,Satisfactory
Robert S,2023-02-05,Project Gamma,Excellent
Olivia P,2023-02-08,Project Delta,Good
Daniel H,2023-02-12,Project Delta,Satisfactory
Sophia L,2023-02-15,Project Delta,Excellent
Matthew C,2023-02-20,Project Eta,Good
Emma G,2023-02-25,Project Theta,Satisfactory
James R,2023-03-03,Project Theta,Excellent
Ella J,2023-03-06,Project Iota,Good

projects table:
Project
Project Alpha
Project Beta
Project Gamma
Project Delta
Project Eta
Project Theta
Project Iota

The screenshots I pasted (expected output) are views processed by Paint tool.