Power Pivot - Select Named Sets via Slicer Error 'Cannot Handle a Set that Changes Dimensionality'

%3CLINGO-SUB%20id%3D%22lingo-sub-1519780%22%20slang%3D%22en-US%22%3EPower%20Pivot%20-%20Select%20Named%20Sets%20via%20Slicer%20Error%20'Cannot%20Handle%20a%20Set%20that%20Changes%20Dimensionality'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1519780%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20a%20technique%20from%20a%20blog%20post%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fssbi-blog.de%2Fblog%2Ftechnical-topics-english%2Fhow-to-control-named-sets-via-slicer-using-mdx%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fssbi-blog.de%2Fblog%2Ftechnical-topics-english%2Fhow-to-control-named-sets-via-slicer-using-mdx%2F%3C%2FA%3E%3C%2FP%3E%3CP%3Eto%20control%20the%20selection%20of%20Named%20Sets%20in%20a%20pivot%20via%20a%20Slicer.%26nbsp%3B%20My%20named%20sets%20contain%20groups%20of%20DAX%20calculated%20measures%2C%20what%20I'm%20wanting%20to%20do%20is%20allow%20users%20to%20select%20between%20comparison%20versions%20i.e%20vs%20Last%20Year%2C%20vs%20Budget%20etc.%20and%20see%20collections%20of%20performance%20measures.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20followed%20the%20instructions%20and%20have%20created%202%20sets%20that%20both%20work%20on%20the%20pivot%2C%20I've%20created%20an%20independent%20table%20to%20house%20the%20set%20names%20and%20a%20calculated%20measure%20in%20DAX%20that%20returns%20the%20slicer%20selection%20and%20verified%20that%20this%20works%20using%20the%20CUBEVALUE%20function%20but%20when%20using%20the%20third%20SlicerSet%20in%20the%20pivot%20table%20I%20always%20get%20an%20error%3B%26nbsp%3B'Cannot%20Handle%20a%20Set%20that%20Changes%20Dimensionality'%20followed%20by%20excel%20crashing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20a%20sample%20workbook%20where%20you%20can%20see%20the%20issue.%20Both%20the%20'MTD%20vs%20LY'%20and%20'MTD%20vs%20Budget'%20sets%20can%20be%20added%20to%20the%20pivot%20table.%26nbsp%3B%20When%20using%20the%20SlicerSet%20in%20the%20pivot%20it%26nbsp%3Bworks%20fine%20when%20selecting%20'MTD%20vs%20Budget'%20in%20the%20Slicer%20but%20gives%20the%20error%20when%20trying%20to%20select%20'MTD%20vs%20LY'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20most%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%20Mike%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1519780%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

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.

 

Attached is a sample workbook where you can see the issue. Both the 'MTD vs LY' and 'MTD vs Budget' sets can be added to the pivot table.  When using the SlicerSet in the pivot it works fine when selecting 'MTD vs Budget' in the Slicer but gives the error when trying to select 'MTD vs LY'.

 

Any help most appreciated!

 

Cheers, Mike

0 Replies