Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
)
Solved! Go to 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
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