Excel使用切片器筛选CUBESET [英] Excel Filter a CUBESET with Slicers

问题描述

基本上，我试图根据切片器在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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！