Help with Conditional Column comparing a date to a disconnected table

[bmoon]

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.

[v-rzhou-msft · Accepted solution]

Hi @bmoon ,

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]

@bmoon , 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