Forum Discussion

chefe's avatar
chefe
Icon for Helper II rankHelper II
9 years ago

Simple relationship does not work (1 fact table, 1 lookup table)

Hi there,

 

  • I have a data/fact table called "IR positions MTM", containing transactions data for several days, including a field which represents the date on which the transaction is settled.
  • Also, there is a date table (lookup table) "Calendar - settlement dates". Marked as Date table in SSDT.
  • I have connected both tables with a 1:many relationship. I made sure both fields are of type "date".

 

So far so good. However, the relationship does not seem to work. I don't know why.

 

There are two measures: 

 

  1. on the fact table itself using the MAX function to get the latest [period_settlement_date]
  2. on the date table, using the LASTDATE function (also tried the MAX function here, didn't work either)

The problem is that only #1 works. #2 always shows the absolutely last entry in the date table. Even trying LASTDATE(ALLSELECTED(...)) didn't work.

 

I have attached a screenshot to this setup below. Does anyone spot the problem? This is driving me crazy, far more complex calculations work, but this doesn't... :/

 

Kind regards, chefe

 

 

 

 

 

 

Need Help

3 Replies

  • chefe's avatar
    chefe
    Icon for Helper II rankHelper II
    9 years ago

    P.S.: 

     

    does it it matter where (on what table that is) the measure is stored?

    • chefe's avatar
      chefe
      Icon for Helper II rankHelper II
      9 years ago
      Apparently not, have tried inputting the formulas into both tables as a measure. Doesn't work either.
      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi chefe,

        Could you please post expected result here?  Do you want to calculate max date of fact table using LASTDATE function? If so, please create measure using DAX below.

        LastDate = LASTDATE(IR positions MTM[period_settlement_date])

        Thanks,
        Lydia Zhang