Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with Conditional Column comparing a date to a disconnected table

I'm trying to create a conditional column that returns a 1 if the issue date is after a selected date in a disconnected table. This is what my data model looks like

This is the DAX I wrote to create a flag, but it seems to return all 0's:

Proj Col BUOFAA =
IF(TM_Project[ActIssueDate]<SAR_Period_DateDim[SAR_START],1,0)
 
SAR_Period_DateDim[SAR_START] is a measure:
SAR_START = LOOKUPVALUE(SAR_Period_DateDim[SAR_Period_Start],SAR_Period_DateDim[SAR_Period], SELECTEDVALUE(SAR_Period_DateDim[SAR_Period]))
 
I don't really understand why it isn't working, but I thought maybe it was related to the disconnected SAR Period Date Dim table. Any help would be appreciated.
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    According to your statement, I know you want to mark your date by 0 or 1 dynamically based on a slicer which is created by an unrelated "SAR_Period_DateDim" Table. From your screenshot I find that [Proj Col BUOFAA] is a calculated column. However, calculated column couldn't show dynamic result by slicer. Here I suggest you to create a measure and then create a table visual to achieve your goal.

    I think [SAR_START] is a measure to catch the [SAR_Period_Start] based on slicer. You can combine these steps in one measure.

    Proj Col BUOFAA =
VAR _Select_Start =
    SELECTEDVALUE ( SAR_Period_DateDim[SAR_Period_Start] )
RETURN
    IF ( MAX ( TM_Project[ActIssueDate] ) < _Select_Start, 1, 0 )

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Check these

     

    if you do no have any related column/ you can use min or max
    In table TM_Project
    Proj Col BUOFAA =
    IF(TM_Project[ActIssueDate]< min(SAR_Period_DateDim[SAR_START]),1,0)

     


    There is a common column
    Proj Col BUOFAA =
    IF(TM_Project[ActIssueDate]< minX(filter(SAR_Period_DateDim,SAR_Period_DateDim[SAR_Period] =TM_Project[SAR_Period]) SAR_Period_DateDim[SAR_START]),1,0)

     

    refer 4 ways to copy data from one table to another
    https://www.youtube.com/watch?v=Wu1mWxR23jU
    https://www.youtube.com/watch?v=czNHt7UXIe8

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    According to your statement, I know you want to mark your date by 0 or 1 dynamically based on a slicer which is created by an unrelated "SAR_Period_DateDim" Table. From your screenshot I find that [Proj Col BUOFAA] is a calculated column. However, calculated column couldn't show dynamic result by slicer. Here I suggest you to create a measure and then create a table visual to achieve your goal.

    I think [SAR_START] is a measure to catch the [SAR_Period_Start] based on slicer. You can combine these steps in one measure.

    Proj Col BUOFAA =
VAR _Select_Start =
    SELECTEDVALUE ( SAR_Period_DateDim[SAR_Period_Start] )
RETURN
    IF ( MAX ( TM_Project[ActIssueDate] ) < _Select_Start, 1, 0 )

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.