2

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.

Example 1:

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

Example 2: with date in cell 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.

Example 1:

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

Example 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")

Example 3:

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

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

Chris
  • 737
  • 3
  • 16
  • 32

2 Answers2

5

Please try:

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

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

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")
GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • I need some more assistance though. I am using this CUBESET with CUBERANKMEMBERS, and I am not able to get the sort order right by just using the Measure. Can you point me in the right direction? – Ejaz Ahmed Jul 05 '20 at 11:03
  • @EjazAhmed try starting a new question – GregGalloway Jul 07 '20 at 01:04
0

I found this approach did the trick for me:

=CUBESET("myDataSource","{[Dimensions].[CostCentre].[New Retail],[Dimensions].[CostCentre].[Used Retail]}","My Caption")

The key part is fully qualifying each item, separated by a comma, within curly braces.

richardprocter
  • 125
  • 1
  • 2