Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure to affect Date Slicer

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

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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