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

     

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