topic Re: Help with Conditional Column comparing a date to a disconnected table in DAX Commands and Tips

Help with Conditional Column comparing a date to a disconnected table

Anonymous — Thu, 24 Feb 2022 19:39:19 GMT

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.

Re: Help with Conditional Column comparing a date to a disconnected table

amitchandak — Fri, 25 Feb 2022 02:46:03 GMT

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

Re: Help with Conditional Column comparing a date to a disconnected table

Anonymous — Tue, 01 Mar 2022 09:31:26 GMT

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.