Excel Filter a CUBESET with Slicers

Question

Basically, I am trying to filter the results of a CUBESET function in Excel according to a slicer, and I'm not really getting anywhere.

I have a two-column table of clients and the state they belong to, and a slicer of states that the user can filter with. There are some other parts of the sheet where I want to display all of the clients in the selected state in the cells, so I'm hoping to use CUBERANKEDMEMBER with a CUBESET that is filtered by the slicer. For a basic case where I hard code a value, I've tried:

= CUBESET("server\Clients", "FILTER([Client List].[FULL_NAME].Members,
[Client List].[State].Currentmember.Membervalue = 'QUEENSLAND')")`

But the result here is empty (which I checked using CUBESETCOUNT). I'm sure that the problem is because the filter iterates through [Client List].[FULL_NAME].Members but I'm trying to filter on [Client List].[State].

If something like this works, what I'd like to do is something like:

= CUBESET("server\Clients", "FILTER([Client List].[FULL_NAME].Members, 
[Client List].[State].Currentmember.Membervalue = Slicer_State)")

knowing that only 1 state will ever be selected at a time.

I don't really know much about MDX so I suspect the answer is there but I'm not sure what to try.

Answer 1

I think in terms of yourfirst hard-coded script:

=CUBESET("server\Clients", "FILTER([Client List].[FULL_NAME].Members, [Client List].[State].Currentmember.Membervalue = 'QUEENSLAND')")

You should be able to use EXISTS: https://docs.microsoft.com/en-us/sql/mdx/exists-mdx

=CUBESET("server\Clients", "EXISTS([Client List].[FULL_NAME].[FULL_NAME].MEMBERS, {[Client List].[State].&[QUEENSLAND]})")

But it is worth harcoding the following, even simpler scripts to check that each element of the above is correct:

=CUBESETCOUNT(CUBESET("server\Clients", "[Client List].[FULL_NAME].[FULL_NAME].MEMBERS")) // should return the number of members in full_name 

=CUBESETCOUNT(CUBESET("server\Clients", "{[Client List].[State].&[QUEENSLAND]}")) // should return 1