If exists in related table, get that value

[guyinazo]

and if not use another value.  Trying to wrap my head around the DAX for this.  I have a related  table of work orders and if that work order is found then I need to grab a date from that table.  If it is NOT found, then I need to just use the current date in the main table.

What I have right now is something like this: 

LastStartDate = VAR WOExists = CALCULATE(COUNTROWS(SecondTable,FILTER(SecondTable, SecondTable[WorkOrder] = EARLIER(MainTable[WorkOrder]))>1

RETURN If(WOExists,SecondTable[WorkOrder],MainTable[WorkOrder])

And I am doing this as a column, not a measure

Thanks for the information

[AllisonKennedy · Accepted solution]

@guyinazo  The direction of the relationship is really important here - what does your data model view look like please? 

From your question it seems like you're wanting to get the last date from the Fact (WO transactions??) table and pull it into the Dimension table that has a list of each WO only once??

If so, you can try:

Last WO Date = 
MAXX( FILTER ( FactTable, DimensionTable[WOid] = FactTable[WOid]), FactTable[Date] )

Then you can nest that in an IF statement so if it's blank, return current date (not sure what you mean by current date in main table - again please provide more info on your relationships).

[smpa01 · Accepted solution]

@guyinazo  If you are creating a calculated column in MainTable, this should work out , works with or without relationship 

LastStartDate =
CALCULATE (
    CALCULATE (
        MAXX ( SecondTable, SecondTable[Date] ),
        TREATAS ( VALUES ( MainTable[WorkOrder] ), SecondTable[WorkOrder] )
    )
)

[guyinazo]

Seems to work, but I am getting a warning "Expressions that yield variant data-type cannot be used to define calculated columns"

[AllisonKennedy · Accepted solution]

@guyinazo  The direction of the relationship is really important here - what does your data model view look like please? 

From your question it seems like you're wanting to get the last date from the Fact (WO transactions??) table and pull it into the Dimension table that has a list of each WO only once??

If so, you can try:

Last WO Date = 
MAXX( FILTER ( FactTable, DimensionTable[WOid] = FactTable[WOid]), FactTable[Date] )

Then you can nest that in an IF statement so if it's blank, return current date (not sure what you mean by current date in main table - again please provide more info on your relationships).

[guyinazo]

But now that I am re-reading your reply, let me try that

[guyinazo]

Not really.  So I have a main table with work orders and other fields.  This looks up two other tables that are related by Work Order.  This main table has a created column called LastStartDate.  If the work order exists IN the first table, use that LastStartDate, else use the LastStartDate from the second table.