Pivot Named Set via Slicer (STRTOSET) 'Cannot Handle a Set that Changes Dimensionality' Error

[Anonymous]

Hi all,

I am trying to use a technique from a blog post;

https://ssbi-blog.de/blog/technical-topics-english/how-to-control-named-sets-via-slicer-using-mdx/

to control the selection of Named Sets in a pivot via a Slicer.  My named sets contain groups of DAX calculated measures, what I'm wanting to do is allow users to select between comparison versions i.e vs Last Year, vs Budget etc. and see collections of performance measures.

I've followed the instructions and have created 2 sets that both work on the pivot, I've created an independent table to house the set names and a calculated measure in DAX that returns the slicer selection and verified that this works using the CUBEVALUE function but when using the third SlicerSet in the pivot table I always get an error; 'Cannot Handle a Set that Changes Dimensionality' followed by excel crashing.

I have no idea why it isn't working!  Any help would be greatly appreciated!

I've created a sample workbook where you can see the issue.  It works fine when selecting MTD vs Budget by gives the error when trying to select vs LY;

https://drive.google.com/file/d/1Xm6dx-qdlVb659VTRYGxWR7-RfdcdGdA/view?usp=sharing

Cheers, Mike

[Anonymous · Accepted solution]

@Anonymous 
I only see the database error from the sample file:

Try verify your Microsoft Access  for any #errors. Review your report before you refresh any data into Excel.

All else, try the following.

1. Re-link your Access database tables to your Excel spreadsheet.
2. Rebuild the ODBC connection.

BTW, for power pivot question, you are unlike to get a solution in Power Bi forum, you should go to excel forums for assistance.

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