RELATED function not working with one-to-many relationship

[psyclown]

Hi,

I've scratched my head for several days now, trying to figure out this problem with RELATED and one-to-many relationship.

My relationship:

My measure:

Indeks salg = (1200000 / 365) * ((DATEDIFF(DATE(YEAR(Today()), 1, 1), Today(), DAY) - 1) - DATEDIFF(DATE(YEAR(Today()), "01", "01"), RELATED('Booking Administration'[IndexDate]), DAY))

Error: Column with name was not found ...

I can't seems figure out what the problem is or how to get around it.

Hopefully someone here can help me in right direction!

Best Regards,

Jesper

[PaulOlding]

Is Indeks salg a measure or calculated column?

If it's a measure then there's this issue:

You have the basic premise right in that RELATED will allow you to get a value from a dimension table (Booking Administration), but... you need to be iterating the fact table (Certus Bordereau) to do it.  You need a row context from the fact table, then RELATED will follow the relationship from the fact (for the current row) to the dimension and get the single value for your column.

Right now, you're not iterating the fact anywhere in your measure.

[psyclown]

@PaulOlding 

Thanks for the quick response, both of you.

How can I supposed to iterate the fact table in this situation?

@amitchandak is mentioning making a column in the many table with the one tables value. I've tried that too and I can make a column in many (Certus Bordereau) with just:

column = RELATED('Booking Administration'[IndexDate])

But my understanding with using a date in a measure, then you need to have it wrapped by something like MAX, MIN, LASTDATE etc and I don't want that, since IndexDate is a unique value for each row in Booking Administration.

[amitchandak]

@psyclown , related work in one to many if you create a column on many sides from the one side Table.

On one side, you can try like this example 

MAxx(RELATEDTABLE('Booking Administration'), [IndexDate])

refer if needed, 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8