Forum Discussion

Krzysztof_Hyla's avatar
6 years ago
Solved

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)
 
 

  • 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
    🙂

     

2 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity 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
    🙂

     

    • Krzysztof_Hyla's avatar
      Krzysztof_Hyla
      Icon for Helper II rankHelper 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