Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
@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/
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |