Reply
Member
Posts: 220
Registered: ‎07-26-2016
Accepted Solution

SAMEPERIODLASTYEAR with filter return error message

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.


Accepted Solutions
Member
Posts: 102
Registered: ‎09-23-2016

Re: SAMEPERIODLASTYEAR with filter return error message

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

View solution in original post


All Replies
Member
Posts: 102
Registered: ‎09-23-2016

Re: SAMEPERIODLASTYEAR with filter return error message

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

Member
Posts: 220
Registered: ‎07-26-2016

Re: SAMEPERIODLASTYEAR with filter return error message

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!