Forum Discussion

NumeritasMartin's avatar
NumeritasMartin
Frequent Visitor
7 years ago

Cubeset with multiple Excel cell inputs

Good Afternoon All,

 

I hope the festive December month is treating you well.

 

I am trying to get my Cubeset to accept multiple Cell entries, I have got two working but can't find or figure out the syntax for multiple.

 

This is what I am currently using;

 

=CUBESET("ThisWorkbookDataModel","[Department].[Department Group].[All].["&F4&"]:[Department].[Department Group].[All].["&F5&"]","Departments")

I also expect this is returning all Departments (not sure of the order) all departments between the two cells.

 

Ideally I would like to be able to populate multiple cells and they will all be incorporated into my cubeset. Example below is that all three departments will be included in my cubeset, but not cause an error if blank.

 

A1Admin
A2Marketing Admin
A3Sales

 

Thanks

 

Martin

Need Help
Tips and Tricks

4 Replies

    • NumeritasMartin's avatar
      NumeritasMartin
      Frequent Visitor
      7 years ago

      Thanks for the reply v-juanli-msft

       

      I that website before but I am struggling to apply it, I can't see where the multiple cells are referenced and I don't understand how the syntax for the 'Exists' function works, I was kind of hoping for an example or a resource which I can learn about how I can apply multiple criteria to the Cubeset Expression.

       

      It is probably worth mentioning that I want to return multiple results from the same column (i.e. Dept 1 and Dept 2 and Dept 3, from the Department Code field).

       

      I'll keep looking to see if I can figure it out.

       

       

       

       

      • NumeritasMartin's avatar
        NumeritasMartin
        Frequent Visitor
        7 years ago

        So by converting the a PIVOT table that I got the following which works unless the cell is Blank.

         

        =CUBESET("ThisWorkbookDataModel","{[Department].[Department Group].&["&$F$4&"],[Department].[Department Group].&["&$F$5&"],[Department].[Department Group].&["&$F$6&"]}","(Multiple Items)")

        This is a workable solution for me however I would like to have a error wrapper for blank cells.

         

        Thanks