问题描述
基本上，我试图根据切片器在Excel中过滤CUBESET函数的结果，但我实际上并没有得到什么.
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.
我有一个两列的客户端表和它们所属的状态，以及一个用户可以过滤的状态切片器.我想在工作表的其他部分显示单元格中处于选定状态的所有客户端，因此我希望将CUBERANKEDMEMBER与由切片器过滤的CUBESET一起使用.对于我用硬编码值的基本情况，我尝试过:
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')")`
但是这里的结果是空的(我用CUBESETCOUNT检查过).我确定问题是因为过滤器会循环遍历[Client List].[FULL_NAME].Members，但是我正在尝试对[Client List].[State]进行过滤.
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.
我对MDX不太了解，所以我怀疑答案在那里，但是我不确定该怎么做.
I don't really know much about MDX so I suspect the answer is there but I'm not sure what to try.
推荐答案
我认为是第一个硬编码脚本:
I think in terms of yourfirst hard-coded script:
=CUBESET("server\Clients", "FILTER([Client List].[FULL_NAME].Members, [Client List].[State].Currentmember.Membervalue = 'QUEENSLAND')")
您应该能够使用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
这篇关于Excel使用切片器筛选CUBESET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！