Forum Discussion

kilala's avatar
kilala
Resolver I
4 years ago
Solved

Add Condition in Calculated Column

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 🙂

 

 

Need Help
Show and Tell
  • kilala's avatar
    kilala
    4 years ago

    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!

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    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?

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    Hi, kilala 

    There are no syntax errors from the current formula.

    Is there any other specific error message?

    Best Regards,
    Community Support Team _ Eason

  • kilala's avatar
    kilala
    Resolver I
    4 years ago

    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!