Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
bmoon
Frequent Visitor

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

bmoon_0-1645731334394.png

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.
1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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.

 

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

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
Super User
Super User

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.