Measure to affect Date Slicer

[Anonymous]

Hi BI Community,

I would like to ask wheather I select a date from dropdown filter it affects two other slicers.

What I mean. My report is run every monday on excel and want to move it to PBI.
Inside Workers table there is Candidate_ID, Start_date, End_Date.

I would like to select ex. 8-Nov-2021 on one dropdown filter and it will limit Start_date before 8-Nov-2021 and all End_Date after 8-Nov-2021.

Curently there are two slicers but I would like to merge it into one dropdown list.

Based what I found in interenet I tried to creat second dates table, two way filtering, a measure (like below) based on selected date but with no success. 

DateSelected = SELECTEDVALUE(DatesTbl[Date])

and later create a column in dataset, but it doesn't work as well.

WorkerFlag =

IF (

'Workers'[Start_date] < DatesTbl[DateSelected],
IF ( 'Workers'[End_date] >= DatesTbl[DateSelected], 1, 0 ),
0

)

I would be thankful if there is a solution for this.

Pawel

[v-easonf-msft · Accepted solution]

Hi, @Anonymous

You need to create a measure rather than a calculated column.

Visual filter(WorkerFlag) = 
IF (
    SELECTEDVALUE ( Workers[Start_date] ) < SELECTEDVALUE ( DatesTbl[Date] )
        && SELECTEDVALUE ( Workers[End_Date] ) >= SELECTEDVALUE ( DatesTbl[Date] ),
    1,
    0
)

Then apply this new meaure to 'filters on this visual' of viusal filter pane.

Count_ID = 
VAR tab =
    FILTER (
        Workers,
        Workers[Start_date] < SELECTEDVALUE ( DatesTbl[Date] )
            && Workers[End_Date] >= SELECTEDVALUE ( DatesTbl[Date] )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( Workers[Calendar_ID] ), tab )

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@Anonymous , I doubt that is possible in the case of a range slicer. Limit the start or end date is not possible 

You can log an idea

https://ideas.powerbi.com/ideas/