cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Krzysztof_Hyla
Helper II
Helper II

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?

 

Show Row in report? =
VAR SelectedDate = max('Calendar'[Date])
RETURN
IF(
AND (Positions[Start]<=SelectedDate, OR (Positions[End] > SelectedDate, ISBLANK(Positions[End]) ) )
,1,0)
 
Visuals.jpg
 

Tables.jpg

1 ACCEPTED SOLUTION
nandukrishnavs
Community Champion
Community Champion

@Krzysztof_Hyla 

 

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
🙂

 

View solution in original post

2 REPLIES 2
nandukrishnavs
Community Champion
Community Champion

@Krzysztof_Hyla 

 

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
🙂

 

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors