Help with DirectQuery and RELATED / LOOKUPVALUE alternatives

Hi DavidAtkins ,

Try this:

Measure = 
VAR RelatedDate_ =
    CALCULATE (
        MAX ( 'FactTable2$'[Date] ),
        TREATAS ( VALUES ( 'FactTable1$'[Key] ), 'FactTable2$'[Key] ),
        'FactTable2$'[Key] <> -1
    )
RETURN
    IF (
        RelatedDate_ <> BLANK (),
        RelatedDate_ + MAX ( 'FactTable1$'[Returns Period] )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

NB The only way I've found to get this to work is to make a summary table like below. I'm sure it's not best practice but it works...

SummaryReturns =
SUMMARIZE (
    FactTable2,
    FactTable2[Key],
    FactTable2[Date],
    "Return Period",
        IF ( FactTable2[Key] = -1, 28,
            IF (
                ISBLANK ( LOOKUPVALUE ( FactTable1[Returns Period], FactTable1[Key], FactTable2[Key] )
                ),
                28,
                LOOKUPVALUE ( FactTable1[Returns Period], FactTable1[Key], FactTable2[Key] )
            )
        )
)