Excel中具有多个条件的CUBESET()函数 [英] CUBESET() function in Excel with Multiple criteria

问题描述

我正在尝试在Excel中创建CUBESET函数，但是我不知道如何使用在同一维度内的多个条件对其进行过滤.到目前为止，这是我使用的一个标准.

I'm trying to create a CUBESET function in Excel, but I don't know how to filter it using multiple criteria within the same dimension. This is what I have so far working with one criteria.

示例1:

=CUBESET("ThisWorkbookDataModel","{[Facebook].[Bucket (C)].[All].[DPA]*[Facebook].[AudienceType (C)].children}","Bucket")

示例2:在单元格C3中具有日期

=CUBESET("ThisWorkbookDataModel","{[Facebook].[Week End].[All].["&TEXT($C$3,"m/d/yyyy")&"]*[Facebook].[Campaign (C)].children}","Campaign Breakout - Weekly")

---

这就是我试图用两个标准来做的，但是没有运气.

---

And this is what I've tried to do with two criteria, but with no luck.

示例1:

=CUBESET("ThisWorkbookDataModel","FILTER( [Facebook].[AudienceType (C)].children,[Facebook].[Week End].[All].["&TEXT($C$3,"m/d/yyyy")&"] && [Facebook].[Bucket (C)].[All].[DPABroadAudience])","Bucket")

示例2:

=CUBESET("ThisWorkbookDataModel","FILTER( [Facebook].[AudienceType (C)].children,AND([Facebook].[Week End].[All].["&TEXT($C$3,"m/d/yyyy")&"],[Facebook].[Bucket (C)].[All].[DPABroadAudience]))","Bucket")

示例3:

=CUBESET("ThisWorkbookDataModel","{[Facebook].[AudienceType (C)].children *[Facebook].[Week End].[All].["&TEXT($C$3,"m/d/yyyy")&"] * [Facebook].[Bucket (C)].[All].[DPABroadAudience]})","Bucket")

---

顺便说一句-虽然我现在只需要两个条件，但很高兴看到一个适用于2个以上条件的解决方案.

---

Btw - while I only need two criteria right now, it would be great to see a solution that would work for 2+ criteria.

推荐答案

请尝试:

=CUBESET("ThisWorkbookDataModel","EXISTS( [Facebook].[AudienceType (C)].children,([Facebook].[Week End].[All].["&TEXT($C$3,"m/d/yyyy")&"], [Facebook].[Bucket (C)].[All].[DPABroadAudience]) )","Bucket")

由于两个过滤器都在同一个Facebook维度中，因此 EXISTS 函数应该可以正常工作.随时从Facebook维度添加其他过滤器.

Since both filters are in the same Facebook dimension the EXISTS function should work. Feel free to add additional filters from the Facebook dimension.

如果您需要按其他维度(而不是Facebook维度)进行过滤，则需要执行以下操作.选择一个度量，该度量将确定过滤器中存在哪些AudienceType.

If you need to filter by other dimensions (not the Facebook dimension) then you will need to do the following. Choose a measure which will determine which AudienceTypes exist with the filters.

=CUBESET("ThisWorkbookDataModel","NONEMPTY( [Facebook].[AudienceType (C)].children,([Measures].[Your Measure], [Facebook].[Week End].[All].["&TEXT($C$3,"m/d/yyyy")&"], [Facebook].[Bucket (C)].[All].[DPABroadAudience], [Other Dimension].[Column Z].[All].[Your Filter]) )","Bucket")

这篇关于Excel中具有多个条件的CUBESET()函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！