Slicer autoselecting

[Anonymous]

Hi,

I have the following situation:

A slicer which let you select "object", these objects have a "time":

Now what I am trying to achieve is that when you click "object A" in the slicer the slicer will automatically remove all other options except for the "object" that is closest to "object A" in terms of "time", in this example this would be "object B". Another example is when you click on "object C" it will again only let you choose the closest one in "time" which is "object D".

(note that when I say closest I mean the closest one that is a higher value than the one selected)

Thanks in advance !

[Eric_Zhang]

---

@Anonymous wrote:

Hi,

I have the following situation:

A slicer which let you select "object", these objects have a "time":

Now what I am trying to achieve is that when you click "object A" in the slicer the slicer will automatically remove all other options except for the "object" that is closest to "object A" in terms of "time", in this example this would be "object B". Another example is when you click on "object C" it will again only let you choose the closest one in "time" which is "object D".

(note that when I say closest I mean the closest one that is a higher value than the one selected)

Thanks in advance !

---

@Anonymous

You can create a calculated table and a measure as below. See more details in the attached pbix.

SlicerTable = VALUES('Table'[Object]) 

isShown =
VAR Tbl1 =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Time]
                >= CALCULATE (
                    MAX ( 'Table'[Time] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Object] = LASTNONBLANK ( SlicerTable[Object], "" )
                    )
                )
        ),
        'Table'[Object],
        "time", MAX ( 'Table'[Time] )
    )
VAR Tbl2 =
    ADDCOLUMNS ( Tbl1, "rank", RANKX ( Tbl1, [time],, ASC, DENSE ) )
VAR Top2 =
    FILTER ( Tbl2, [rank] <= 2 )
RETURN
    IF (
        ISFILTERED ( SlicerTable[Object] ) && HASONEVALUE ( SlicerTable[Object] )
            && CONTAINS ( Top2, 'Table'[Object], LASTNONBLANK ( 'Table'[Object], "" ) ),
        1,
        BLANK ()
    )

[Anonymous]

Hi @Eric_Zhang,

I can't get your formula to work since the value "Time" I spoke of was a measure not a normal column. Sorry it is stupid of me that I forgot to mention this detail. The measure looks something like this:

Time = MAX(table[avgTimePoint]) - MIN(table[avgTimePoint])

Now when I try to create "column" like in your example PBIX I get the message: "Column "Time" in table 'table' cannot be found or may not be used in this expression.

Another question I have about your formula is, in the calculated column you use:  'Table'[Object]="C" why C because it is the middle one or ? I'd like to understand this since my actual file doesn't use A B C D E but other names for these objects(which are alot more then 5).

 Regards,

L. Meijdam