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

[AndrewP]

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?

[Sean]

@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" )

Good Luck!

HTH

[AndrewP]

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]

@AndrewP

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

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

[AndrewP]

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]

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"
)

 

[AndrewP]

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]

@AndrewP

Ok, now have beter understand of your problem.

1. Create a Table for slicer

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 ()
        )
    )
)

[AndrewP]

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]

@AndrewP

Using with other visuals

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.

[AndrewP]

I'm not sure I'm understanding what you did in that picture.

Let me give an example of one of my visuals.

I have a pie chart.

Legend is: ticket_source

Value is: %GT Count of ticket_id

What do I need to do to this chart to apply the slicer choice to it, so it only counts tickets of the correct age?

If it can't be done on as a page filter, I could apply it to each indivdually, that won't be a problem.

[Vvelarde]

@AndrewP

In visual level filter put the measure created and select is not blank.

To a better and faster way to help you:

Share sample data and expected result.

[AndrewP]

I tried that.

In every visual exept for one line chart, no matter what's selected, the chart goes blank.

I'm not sure what i'm doing wrong.

http://i.imgur.com/XFNlUcm.png

The data I'm working with isn't quite something I can share, but I can describe it.

Source: the word email, web, phone, or other

ticket_id: index field for all tickets, sequential integer

TicketAge: a calculated colum for the difference in days between the creation date and TODAY()

Values-FilterSlicer: the example for the measure you gave me, changed to reference the custom table along with the TicketAge column.

[Vvelarde]

@AndrewP

For sample this measure works in a chart where legend is Type of Ticket and Values are count of ticket id.

CountRows-Filter =
CALCULATE (
    COUNTROWS ( Table1 ),
    Table1[TicketAge] <= VALUES ( RangeAge[Max] )
)

[AndrewP · Accepted solution]

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

[AndrewP]

Is there no way to universally do this for all visuals on a page?

Having a custom measure for each invidivual item seems horribly inefficient, where as with a raw SQL query I could generate exactly the results I'm looking for.

[Vvelarde]

@AndrewP

The last measure works for several scenarios. Maybe in your report is not necessary create a measure-filter for each visual, depends of your context and data model.

Like i said in previous post for the moment (accord to my knowledge) use a measure in a page level filter.