cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

 

2017-04-07 10_46_32-chec_spot_valuta.xlsx - Excel.png

 

 

 

 

 

3 REPLIES 3
Highlighted
Helper II
Helper II

P.S.: 

 

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

Highlighted

Apparently not, have tried inputting the formulas into both tables as a measure. Doesn't work either.
Highlighted

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors