Cubeset Formula Help Needed to Find Unique Values

Harris, Darin 1 Reputation point
2020-12-28T20:50:45.83+00:00

Looking for help on a CUBESET formula.

This formula gives me an alphabetical list of all the brand children that appear in month1 or month2 and there are duplicates. I'm looking to only get a unique list of whether the brand children exist in either month1 or month2.

CUBESET("ThisWorkbookDataModel","({[Programs].[Months].[Month1],[Programs].[Months].[Month2]},([Programs].[Division].[Division1],[Programs].[Brand].Children))","Brands",3)

thank you for any help!

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,362 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Harris, Darin 1 Reputation point
    2020-12-29T15:41:38.32+00:00

    @Emily Hua-MSFT It wouldn't accept a reply so typing a response here.

    I don't have the ability to share a file or screenshots so hopefully this is enough to solve:

    This CUBESET formula in conjunction the CUBERANKEDMEMBER gives me a master list of all brands from Month1 and Month2. The opportunity is that some brands appear in both months and I need a unique list for my project. I thought the brackets { } would combine to a unique list but instead it gives me duplicates as some brands are listed in both months.

    • CUBESET("ThisWorkbookDataModel","({[Programs].[Months].[Month1],[Programs].[Months].[Month2]},([Programs].[Division].[Division1],[Programs].[Brand].Children))","Brands",3)
    • CUBERANKEDMEMBER("ThisWorkbookDataModel",Brands,1)
    • CUBERANKEDMEMBER("ThisWorkbookDataModel",Brands,2)
      etc.

    In this example, brand 1 and 2 is the same because it appears in Month1 and Month2. User's select up to 24 months, two at a time so a unique list is essential and it can't be a 'work around' for just Month1 and Month2 labeled items.

    thank you!