cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndrewP Regular Visitor
Regular Visitor

Is there any way to get a slicer where applied value is less than or equal to what's chosen?

I'm trying to use a Slicer such that whatever is chosen, what it ends up showing is everything less than or equal to what's chosen rather than only that.

 

For example, if I check "5" from the slicer, I want to see 5, 4, 3, 2, and 1 in the data on the page.

 

Or vice versa, if I check "5" I want to see 5, 6, 7, 8, 9, etc

 

Is this possible?

1 ACCEPTED SOLUTION

Accepted Solutions
AndrewP Regular Visitor
Regular Visitor

Re: Is there any way to get a slicer where applied value is less than or equal to what's chosen?

I THINK I got it working

 

I used

 

TA_Data = 
IF (    HASONEVALUE(age_slicer_values[Range]),
        CALCULATE(
                    COUNTROWS('dev ost_ticket'),
                    'dev ost_ticket'[TicketAge] <= values(age_slicer_values[Value])
                    ),
        COUNTROWS('dev ost_ticket')
        )

For the charts using counts and

TA_Filter = 
IF ( HASONEVALUE(age_slicer_values[Range]) && HASONEVALUE('dev ost_ticket'[TicketAge]), IF ( VALUES('dev ost_ticket'[TicketAge]) <= VALUES(age_slicer_values[VALUE]), 1, 0 ), 1 )

as a visual filter for those using Sums

View solution in original post

16 REPLIES 16
Sean Super Contributor
Super Contributor

Re: Is there any way to get a slicer where applied value is less than or equal to what's chosen?

@AndrewP

You need a COLUMN to create a Slicer!

So create a Slicer COLUMN that checks your original column for whatever condition(s) you need and then returns what you want.

So it should look somehting like this in general (you can nest IFs or use SWITCH instead)

 

Slicer COLUMN =
IF ( 'Table'[Original Column] <= 5, "< 5", "> 5" )

Slicer Column.png

 

Good Luck! Smiley Happy

HTH

AndrewP Regular Visitor
Regular Visitor

Re: Is there any way to get a slicer where applied value is less than or equal to what's chosen?

It sort of works, but it doesn't give the results you'd expect.

 

It's filtering on the value in the 'cell', not the expressions

 

So if I end up with three cells <5, <7, <9

 

and I select <9, it does not include <7 and <5

 

Vvelarde Super Contributor
Super Contributor

Re: Is there any way to get a slicer where applied value is less than or equal to what's chosen?

@AndrewP

 

Hi, I'm not sure that is what you want but check this file uploaded.

 

Image.png

 

https://drive.google.com/file/d/0B95C8CKdTZE3RFFNOUd1Vy0tczg/view?usp=sharing




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




AndrewP Regular Visitor
Regular Visitor

Re: Is there any way to get a slicer where applied value is less than or equal to what's chosen?

That's not what I'm trying to achieve.

I have a current table with a column called Age, with age in days 0 - X days

I want to have a slicer for things within 30 days, 60, days, 90 days, 365 days.

Checking 30 should show everything 30 and below, checking 60 should show everything 60 and below, and so on.

 

I can't generate the slicer automatically using anything, but possibly I could adapt the measure you did to achieve what I want.

 

EDIT: it doesn't work as a page level filter, nor does it work on certain types of visuals for whatever reason 😕

Sean Super Contributor
Super Contributor

Re: Is there any way to get a slicer where applied value is less than or equal to what's chosen?

Age Slicer =
SWITCH (
    TRUE (),
    'Table'[Age] <= 30, "30 Days",
    'Table'[Age] > 30 && 'Table'[Age] <= 60, "60 Days",
    'Table'[Age] > 60 && 'Table'[Age] <= 90, "90 Days",
    'Table'[Age] > 90 && 'Table'[Age] <= 365, "365 Days",
    "Over 365"
)

 

Slicer Column 2.png 

AndrewP Regular Visitor
Regular Visitor

Re: Is there any way to get a slicer where applied value is less than or equal to what's chosen?

Same problem.

 

If I select 60, it shows me things 31-60.

 

I need 30 to show 0-30

I need 60 to show 0-60

etc

 

The actual column gets filled in correctly, but the slicer seems is the issue here.

Vvelarde Super Contributor
Super Contributor

Re: Is there any way to get a slicer where applied value is less than or equal to what's chosen?

@AndrewP

 

Ok, now have beter understand of your problem.

 

1. Create a Table for slicer

 

Slicers-Age.png

 

2. Create a measure: 

 

Values-FilterSlicer =
IF (
    AND ( HASONEVALUE ( Table1[Name] ), HASONEVALUE ( 'Slicers-Age'[Range] ) ),
    (
        IF (
            VALUES ( Table1[Values] ) <= VALUES ( 'Slicers-Age'[Max] ),
            VALUES ( Table1[Values] ),
            BLANK ()
        )
    )
)

Age.png




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




AndrewP Regular Visitor
Regular Visitor

Re: Is there any way to get a slicer where applied value is less than or equal to what's chosen?

That seems to work in a table, but I'm not sure how to get it to apply to another visual (and I'm trying to get it to act as a page level filter).

Vvelarde Super Contributor
Super Contributor

Re: Is there any way to get a slicer where applied value is less than or equal to what's chosen?

@AndrewP

 

Using with other visuals

 

img2.png

 

 

Another thing: A measure can't be applied in a Page Filter. And a calculated column is filled when the model is loaded so not change with the selection in a filter or slicer.

 

 

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)