Slicer autoselecting

---

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