Creating new table in MS Excel 2013 PowerPivot - Page 2

Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Creating new table in MS Excel 2013 PowerPivot

  1. #11
    New Member
    Join Date
    Feb 2012
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating new table in MS Excel 2013 PowerPivot


    Creating new table in MS Excel 2013 PowerPivot
    Quote Originally Posted by FranzV View Post
    I believe we need some help from a DAX expert. I know that Matt Allington is an active member of the forum that could surely help in finding the solution, but any DAX expert is welcome to help.

    Have you tried using the dummy file?
    I have created a dummy/sample file, but where can I download to, as I can't put it as an attachment here.

  2. #12
    Board Regular
    Join Date
    Dec 2016
    Location
    Mexico
    Posts
    178
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating new table in MS Excel 2013 PowerPivot

    I was refering to the dummy file I created and uploaded to Dropbox that you can download using this link. After opening it, go into Powerpivot to check if it works properly.

    If you want to upload your file try using Dropbox. It's easy.
    FranzV

    Enjoy Excel

    Please give me some feedback. A like will do, but a reply will always be welcomed.

    Special thanks to The Italians, Imke Feldmann, Rob Collie, Bill Jelen, Debra Dalgeish, Chandoo, Ken Puls, Chris Webb, Matt Allington, Kasper De Jong, Chris Newman, and so many others that generously share their talent and guide us in finding solutions with Excel.

  3. #13
    New Member
    Join Date
    Feb 2012
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating new table in MS Excel 2013 PowerPivot

    Hi FranzV,

    I tried download your and when look at it, it's all #ERROR.I
    As well, when I delete the formula and copy back, it's giving me the same error.

    I'm wondering if it's because of my PowerPivot? I'm using MS Excel 2013

    The dropbox link is https://www.dropbox.com/s/53347uvb65...ount.xlsx?dl=0

    Quote Originally Posted by FranzV View Post
    I was refering to the dummy file I created and uploaded to Dropbox that you can download using this link. After opening it, go into Powerpivot to check if it works properly.

    If you want to upload your file try using Dropbox. It's easy.

  4. #14
    Board Regular
    Join Date
    Dec 2016
    Location
    Mexico
    Posts
    178
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating new table in MS Excel 2013 PowerPivot

    Quote Originally Posted by dluhut View Post
    I'm wondering if it's because of my PowerPivot? I'm using MS Excel 2013
    I am sorry to have insisted so much in using my approach before checking its compatibility with your Excel version. Indeed, variables were introduced to DAX in may 2015. You can read about it on this article by The Italians, whose site is one of the best references for DAX. A workaround is nesting each variable's code into the next, resulting in the following code:
    Code:
    HelperTable[Account] =
    SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE (
                    UNION (
                        DISTINCT ( TableA[Account] ),
                        DISTINCT ( TableB[Account] ),
                        DISTINCT ( TableC[Account] )
                    ),
                    [Account]
                ),
                "Index", RANKX (
                    SUMMARIZE (
                        UNION (
                            DISTINCT ( TableA[Account] ),
                            DISTINCT ( TableB[Account] ),
                            DISTINCT ( TableC[Account] )
                        ),
                        [Account]
                    ),
                    [Account],
                    ,
                    TRUE
                )
            ),
            [Index] = 'HelperTable'[Index]
        ),
        [Account]
    )
    Which works for Excel 2016 as well. Now the only problem is the UNION function that was also added in the 2016 version. Jason Thomas wrote an article on simulating the UNION operation in Excel 2013 that might be of use.
    FranzV

    Enjoy Excel

    Please give me some feedback. A like will do, but a reply will always be welcomed.

    Special thanks to The Italians, Imke Feldmann, Rob Collie, Bill Jelen, Debra Dalgeish, Chandoo, Ken Puls, Chris Webb, Matt Allington, Kasper De Jong, Chris Newman, and so many others that generously share their talent and guide us in finding solutions with Excel.

  5. #15
    Board Regular
    Join Date
    Dec 2016
    Location
    Mexico
    Posts
    178
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating new table in MS Excel 2013 PowerPivot

    I tried using Jason Thomas's UNION method and after some tweaking I got it right. I updated the Dropbox dummy with the new formula. The code is much longer, but it should work in Excel 2013. Please let me know if it did.

    Code:
    =SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE (
                    ADDCOLUMNS (
                        CROSSJOIN (
                            ADDCOLUMNS (
                                SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                                "B1", RANKX (
                                    SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                                    [A1],
                                    ,
                                    1,
                                    DENSE
                                )
                            ),
                            ADDCOLUMNS (
                                SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                                "B2", RANKX (
                                    SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                                    [A2],
                                    ,
                                    1,
                                    DENSE
                                )
                            ),
                            ADDCOLUMNS (
                                SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                                "B3", RANKX (
                                    SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                                    [A3],
                                    ,
                                    1,
                                    DENSE
                                )
                            )
                        ),
                        "Account", SWITCH ( TRUE (), [B3] = 1, [A2], [B2] = 1, [A1], [A3] )
                    ),
                    [Account]
                ),
                "Index", RANKX (
                    SUMMARIZE (
                        ADDCOLUMNS (
                            CROSSJOIN (
                                ADDCOLUMNS (
                                    SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                                    "B1", RANKX (
                                        SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                                        [A1],
                                        ,
                                        1,
                                        DENSE
                                    )
                                ),
                                ADDCOLUMNS (
                                    SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                                    "B2", RANKX (
                                        SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                                        [A2],
                                        ,
                                        1,
                                        DENSE
                                    )
                                ),
                                ADDCOLUMNS (
                                    SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                                    "B3", RANKX (
                                        SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                                        [A3],
                                        ,
                                        1,
                                        DENSE
                                    )
                                )
                            ),
                            "Account", SWITCH ( TRUE (), [B3] = 1, [A2], [B2] = 1, [A1], [A3] )
                        ),
                        [Account]
                    ),
                    [Account],
                    ,
                    1
                )
            ),
            [Index] = 'HelperTable'[Index]
        ),
        [Account]
    )
    FranzV

    Enjoy Excel

    Please give me some feedback. A like will do, but a reply will always be welcomed.

    Special thanks to The Italians, Imke Feldmann, Rob Collie, Bill Jelen, Debra Dalgeish, Chandoo, Ken Puls, Chris Webb, Matt Allington, Kasper De Jong, Chris Newman, and so many others that generously share their talent and guide us in finding solutions with Excel.

  6. #16
    New Member
    Join Date
    Feb 2012
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating new table in MS Excel 2013 PowerPivot

    Beautiful!!!

    It works FranzV!!!

    Another like again for you!!!

    Quote Originally Posted by FranzV View Post
    I tried using Jason Thomas's UNION method and after some tweaking I got it right. I updated the Dropbox dummy with the new formula. The code is much longer, but it should work in Excel 2013. Please let me know if it did.

    Code:
    =SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE (
                    ADDCOLUMNS (
                        CROSSJOIN (
                            ADDCOLUMNS (
                                SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                                "B1", RANKX (
                                    SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                                    [A1],
                                    ,
                                    1,
                                    DENSE
                                )
                            ),
                            ADDCOLUMNS (
                                SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                                "B2", RANKX (
                                    SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                                    [A2],
                                    ,
                                    1,
                                    DENSE
                                )
                            ),
                            ADDCOLUMNS (
                                SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                                "B3", RANKX (
                                    SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                                    [A3],
                                    ,
                                    1,
                                    DENSE
                                )
                            )
                        ),
                        "Account", SWITCH ( TRUE (), [B3] = 1, [A2], [B2] = 1, [A1], [A3] )
                    ),
                    [Account]
                ),
                "Index", RANKX (
                    SUMMARIZE (
                        ADDCOLUMNS (
                            CROSSJOIN (
                                ADDCOLUMNS (
                                    SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                                    "B1", RANKX (
                                        SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                                        [A1],
                                        ,
                                        1,
                                        DENSE
                                    )
                                ),
                                ADDCOLUMNS (
                                    SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                                    "B2", RANKX (
                                        SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                                        [A2],
                                        ,
                                        1,
                                        DENSE
                                    )
                                ),
                                ADDCOLUMNS (
                                    SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                                    "B3", RANKX (
                                        SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                                        [A3],
                                        ,
                                        1,
                                        DENSE
                                    )
                                )
                            ),
                            "Account", SWITCH ( TRUE (), [B3] = 1, [A2], [B2] = 1, [A1], [A3] )
                        ),
                        [Account]
                    ),
                    [Account],
                    ,
                    1
                )
            ),
            [Index] = 'HelperTable'[Index]
        ),
        [Account]
    )

  7. #17
    Board Regular
    Join Date
    Dec 2016
    Location
    Mexico
    Posts
    178
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating new table in MS Excel 2013 PowerPivot

      
    I'm glad to have helped. I have to admit it was quite challenging and an excellent opportunity to practice table operations I usually do with Power Query.
    FranzV

    Enjoy Excel

    Please give me some feedback. A like will do, but a reply will always be welcomed.

    Special thanks to The Italians, Imke Feldmann, Rob Collie, Bill Jelen, Debra Dalgeish, Chandoo, Ken Puls, Chris Webb, Matt Allington, Kasper De Jong, Chris Newman, and so many others that generously share their talent and guide us in finding solutions with Excel.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com