Add Condition in Calculated Column

[kilala]

Hi all,

I have 2 Fact Tables, FactA and FactB. FactB needs to get info from FactA. Both FactA and FactB can connect via PayerKey. In FactA, there is DateKey, referring to reportingdate. In FactB, the Datekey is referring to OrderDate. 

I want to create a calculated column in FactB, that gets the [Measure] which logic is from FactA, where datekey in FactA is the latest date in FcatA.

Current formula I used is as below, but it is wrong:
var latestdate = MAX(FactB[DateKey])

return

IF(

CALCULATE([Measure], USERELATIONSHIP(vw_FactB[PayerKey], vw_DimPayer[PayerKey]),vw_FactA[DateKey]=latestdate) = BLANK(), 0,

CALCULATE([Measure], USERELATIONSHIP(vw_FactB[PayerKey], vw_DimPayer[PayerKey]),vw_FactA[DateKey]=latestdate)

)

Please help 🙂

[kilala · Accepted solution]

Hi everyone, 

I tried to use this formula and apparently it works! 

CALCULATE([Measure],ALLEXCEPT(vw_FactB,VW_DIMPAYER[PayerKey]))

I think I dont have connect to FactB since I already have it in my measure.

Thanks everyone!

[v-easonf-msft]

Hi, @kilala 

There are no syntax errors from the current formula.

Is there any other specific error message?

Best Regards,
Community Support Team _ Eason

[MFelix]

Hi @kilala ,

When you refer to t eh MAX date your context since it's on a table it will pick up the current line context so your line will return the exact value, is that the intedend result?

Can you please share a mockup of your data?