cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Suyheng Frequent Visitor
Frequent Visitor

Lookup values in Dax (No Relationship in two tables)

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)
)

Power Pivot Diagram View.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Lookup values in Dax (No Relationship in two tables)

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

1 REPLY 1
Community Support Team
Community Support Team

Re: Lookup values in Dax (No Relationship in two tables)

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