Forum Discussion
Calculated column condition with date from slicer
Goal:
I want to add a calculated column to my table (if statement) based on the date slicer selection.
This will then allow me to filter the table and show only the positions that were active on a particular date in history.
Current State:
I have a 'Positions' table with the history of employee positions (job titles) in our company.
It has a [Start] and [End] date column for each position (there are multiple entries per employee)
The [End] dates for current positions are empty.
The 'Calendar' table is disconnected from the 'Positions' table.
The date slicer type is 'Before'
The additional slicer 'Show Row in Report?' is coming from the new column and the intention is to keep the '1' value selected.
Problem:
My formula seems to ignore the slicer selection.
Only the ISBLANK(Positions[End]) conditions seems to work.
How can I make it work?
Create the below measure.
Show Row in report? = VAR SelectedDate = MAX ( 'Calendar'[Date] ) RETURN IF ( AND ( SELECTEDVALUE ( 'Positions'[Start] ) <= SelectedDate, OR ( SELECTEDVALUE ( 'Positions'[End] ) > SelectedDate, ISBLANK ( Positions[End] ) ) ), 1, 0 )
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
2 Replies
- nandukrishnavs
Community Champion
Create the below measure.
Show Row in report? = VAR SelectedDate = MAX ( 'Calendar'[Date] ) RETURN IF ( AND ( SELECTEDVALUE ( 'Positions'[Start] ) <= SelectedDate, OR ( SELECTEDVALUE ( 'Positions'[End] ) > SelectedDate, ISBLANK ( Positions[End] ) ) ), 1, 0 )
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂- Krzysztof_Hyla
Helper II
Thank you! This has helped me a lot.
I've looked around and I understand now that I cannot directly control column calculation based on slicer value