SAMEPERIODLASTYEAR with filter return error message

[webportal]

Hello,

My data model has a column "CompanyName" and a series of accounting transactions.

I have 2 measures:

• Sales = CALCULATE(SUM('Table'[Balance]); 'Table'[AccountID]=71)
• Sales LY = CALCULATE(Sales; SAMEPERIODLASTYEAR('Calendar'[Date]))

They both work well, but the report page contains a slicer with the field "CompanyName".

When the slicer is marked (ie the only company in the model is selected), the function SAMEPERIODLASTYEAR breaks and we get the error:

MdxScript(Model) (110,61) Error in measure Sales LY: SAMEPERIODLASTYEAR requires a contiguous selection when the date column is in one side of a bidirectional relationship.

This doesn't make sense to me, because when the slicer selects the only company in the model, it should be selecting all the rows in the "Sales" table. I have the calendar table "Calendar" marked as Date.

[magsod · Accepted solution]

Do you have the relationship between Sales and Calendar as a bidirectional relationship?

If so you should change that to a single direction relationship.

Br,

Magnus

[webportal]

That solved it! 

🙂

It turns out the cardinality of this relationship was 1 to 1 and bidirectional.

Changing it to many to one and unidirectional solved the problem!

But why is this?

Thank you for your help!