Lookup values in Dax (No Relationship in two tables)

[Suyheng]

Dear All,

Could you help on solving the dax code for the calulation below:

I want to caluclate the sum of revenue collect for year 2018 for the sale in year <2017 where my sale year is in HS table.

CALCULATE(SUM(NHSRev[TPaid]),NHSRev[R Year]>2018,
HS[SPA Date (Year)]<"2017",
    NHSRev[SID Ref]<>BLANK(),
        NHSRev[Sale Type]="Payoff",
CROSSFILTER(HS[SPA Date],'Calendar'[Date],Both)
)

[v-yuta-msft · Accepted solution]

Hi Suyheng,

Based on your description, you want to enable the indicated relationship between two tables in DirectQuery/Live Connection mode, right?

Suppose NHSRev[SID Ref] and HS[SID Ref]) have default relationship between each other, to achieve your requirement, you can use USERELATIONSHIP() instead of LOOKUPVALUE() like below:

CALCULATE(SUM(NHSRev[TPaid]),NHSRev[R Year]>2018,
HS[SPA Date (Year)]<"2017",
    NHSRev[SID Ref]<>BLANK(),
        NHSRev[Sale Type]="Payoff",
CROSSFILTER(HS[SPA Date],'Calendar'[Date],Both),

USERELATIONSHIP(NHSRev[SID Ref], HS[SID Ref])
)

Best Regards,

Jimmy Tao