Issues with Filter with using Cubeset formula to query a power pivot

[AUSSW]

Newish to power query and power pivot and really struggling when trying to extract information back out of my power pivot/data model into the workbook.

=CUBESET("ThisWorkbookDataModel","{Filter([Cost_Category_Query].[Index].Children,[Cost_Category_Query].[BRAND].currentmember =BRAND)}","Set")

There is a query called 'Cost_category_Query' which does have the two columns in question i am trying to get all of the index values into a set which are the correct brand. The issue is that this set, using CubSetCount(), comes back with 1560 responses if I put a value in Brand that is either the word BRAND or one of the brands I have, but it doesn't filter. e.g. if i put in BRANDA i get 1560 values, if i put in BRANDB i get 1560 values, if i put in junk such as SDGSDG i get 0 values.

I have read a lot of tutorials and watched a bunch of clips but i can't seem to wrap my head around what i am doing wrong in the syntax here to make my filter into a select all as long as it exists somewhere in that query rather than its intended purpose of selecting ONLY the indexes for items in the Brand i control.

 

[Matt Allington]

My observation is that you are using new tech (Power Pivot) with older tech (cubesets). Have you considered using DAX with power pivot and stop using cubesets? Yes, there’s stuff to learn, but I think it would be easier.

 

[RoryA]

FWIW, I think this should work:

=CUBESET("ThisWorkbookDataModel","{[Cost_Category_Query].[Index].[Index].Members*[Cost_Category_Query].[BRAND].[BRANDA]}","Set")