Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Zfilter
New Member

Using a slicer and filtering if the Number is between two numbers

I have a data table with a Start Year Column and End year Column I want to be able to Create a slicer that when I select a year it finds all rows between or equal to the start year and below or equal to the end year. Im not sure if creating a seperate key table with the years that I would be able to select from and linking it by some comparison. 

 

Year Comp 2 is just a seperate Table with possible years I would like to be able to slice by.

Table 1

Zfilter_0-1644011106330.png

Table 2

Zfilter_1-1644011135099.png

 

 

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @Zfilter ,

 

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

 

If the problem is still not resolved, please try the following measure:

 

Measure = 
VAR yearcomp =
    SELECTEDVALUE ( 'Table 2'[Year Comp 2] )
RETURN
    IF (
        yearcomp = BLANK (),
        1,
        COUNTROWS (
            FILTER (
                'Table 1',
                'Table 1'[StartYear] <= yearcomp
                    && 'Table 1'[EndYear] >= yearcomp
            )
        )
    )

 

 

 Then in the visual-level filter, show items when the Measure is 1.

 

vkkfmsft_0-1644372412756.png

 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-kkf-msft
Community Support
Community Support

Hi @Zfilter ,

 

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

 

If the problem is still not resolved, please try the following measure:

 

Measure = 
VAR yearcomp =
    SELECTEDVALUE ( 'Table 2'[Year Comp 2] )
RETURN
    IF (
        yearcomp = BLANK (),
        1,
        COUNTROWS (
            FILTER (
                'Table 1',
                'Table 1'[StartYear] <= yearcomp
                    && 'Table 1'[EndYear] >= yearcomp
            )
        )
    )

 

 

 Then in the visual-level filter, show items when the Measure is 1.

 

vkkfmsft_0-1644372412756.png

 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin
Super User
Super User

Feed your slicer from 'Table 2'[Year Comp 2]  (keep that table disconnected from your data model) . Create a measure that implements your logic against SELECTEDVALUE('Table 2'[Year Comp 2]).  Then add that measure to the page level filters.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors