Home Forums Power Pivot Using Slicers to Filter a CUBESET

This topic contains 4 replies, has 4 voices, and was last updated by  nrizvi110 2 months, 2 weeks ago.

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #7013

    james_b84
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Hi,

    Been using Powerpivot a while now, but this is my first post.

    I’m having trouble with something that seems fairly straightforward (& I think I might have achieved in the past) – but I can’t seem to solve.

    I have a fairly small data model looking at football/soccer data – when looking at matches I am referencing 2 identical dimension tables [Home_Team] + [Away_Team] but these are combined to get the overall results. However, this structure means the measures I want to sort on don’t exist in the datamodel (yet!)

    I am able to create a table showing exactly the data I’m after using a set of CUBEVALUE formulae e.g.

    =N(CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Count of id]”,”[Home_Team].[team_short_name].&[“&$D14&”]”,slicers)+CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Count of id]”,”[Away_Team].[team_short_name].&[“&$D14&”]”,slicers))

    Where D14 contains a specific team name. What I want to do now is create a CUBESET that returns only the team names & then use CUBERANKEDMEMBER to display these in order.

    How can I filter the CUBESET  with the slicer values??? (showing the country & season – further dimensions)

    I’ve had a look round lots of other posts including using tuples, or possibly creating a separate table using DAX, but have yet to find something that works, so would welcome any help.

    Many Thanks!

    #7015

    williamfuu
    Participant
    • Started: 4
    • Replies: 11
    • Total: 15

    Hey James,

    I’ve done something similar at my job but I don’t have access to the file right now.

    Maybe your problem is more complicated than I understand, but I think you can simply use the slicer as an argument on your CUBEVALUE functions. So instead of referencing a cell with your CUBESET, you use your slicer name as an argument (should be something like “Slicer_Country”)

    #7016

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2554
    • Total: 2561

    James,

    As you know, the syntax for CUBESET is:

    = CUBESET(Connection, Set_Expression, [Caption], [Sort_Order], [Sort_By])

    where Set_Expression can be a slicer reference.

    Rob has written about CUBESET and CUBERANKED member at this link.

    #7020

    james_b84
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Hi,

    Thanks for responses – yes I had seen that thread before & it’s kinda what I’m trying to do – 300 level: create a dynamic sorting table.

    Having looked through again it’s this  comment from Geb that best describes what I’m trying to do;
    <div class=”comment-author vcard”><b class=”fn”>geb</b></div>
    <div class=”comment-metadata”><time datetime=”2010-02-05T10:42:44+00:00″>February 5, 2010 at 10:42 am</time></div>
    <div class=”comment-metadata”></div>
    <div class=”comment-metadata”>Using a tuple in a CUBESET to create a dynamic list based on what has been selected in slicers. Still not quite there, but getting closer.</div>
    <div class=”comment-metadata”></div>

    #12483

    nrizvi110
    Participant
    • Started: 0
    • Replies: 1
    • Total: 1

    Has anyone come up with a solution to James_b84 slicer in cubeset problem.

    Set based on Filtered measure value and dimension:

    =CUBESET(“ThisWorkbookDataModel”,”exists(filter([Table1].[EMPNO].MEMBERS,[Measures].[Amt]>50),[Table1].[DEPT].[All].[3])”,”Test”)

    Set based on slicer values:

    =CUBESET(“ThisWorkbookDataModel”,Slicer_DEPT)

    Is there anyway to combine both into one cubeset formula?  Nesting is fine, some wizardry seems to be required to figure out the correct syntax.

     

    Please help

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.