Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: One to many: every "many" displaying for every "one"

  1. #1
    New Member
    Join Date
    Sep 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default One to many: every "many" displaying for every "one"


    One to many: every "many" displaying for every "one"
    Hi All,
    I'm looking for assistance with a modelling question that I've hit a few times in Powerpivot: displaying only *related* row labels from the many side of the relationship, as a subordinate row label to the "one" side records. I have an example model screengrabbed that I think makes the request very clear.
    Thanks!

  2. #2
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,776
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: One to many: every "many" displaying for every "one"

    I can-t see any screenshot but have a look at the RELATEDTABLE function in DAX
    Excel and BI blog: http://xcelanz.com/

  3. #3
    New Member
    Join Date
    Sep 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: One to many: every "many" displaying for every "one"

    Quote Originally Posted by VBA Geek View Post
    I can-t see any screenshot but have a look at the RELATEDTABLE function in DAX
    I'm not sure what I'm doing wrong for the screenshot function -- here is the URL: https://ibb.co/hhph4p

    Meanwhile, I will check out RELATEDTABLE. Thanks for responding!

  4. #4
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,776
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: One to many: every "many" displaying for every "one"

    The scenario you are facing is known as 'AutoExist'. (One good example is HERE).
    Basically the solution to your problem is to denormalize the Budget Description and add it to the right table in your screen shot ( ' many side' ). So you have to add an extra column to this table and use the RELATED function to pull the Budget Description from the left table. Then use this column in your pivot table in place of the Description column from the left table. Once you do this you should be getting the correct pairs only.
    Excel and BI blog: http://xcelanz.com/

  5. #5
    New Member
    Join Date
    Sep 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: One to many: every "many" displaying for every "one"

    I pulled in the Budget field using RELATED, and this does correct the repeating row label; however, it appears that the same aggregation/BudgetAmt summation problem persists, but now at the BudgetAmt grand total level, instead of subtotal level. I have tried thinking through a different data model scheme, but I end up concluding that my relationship is already one to many; so a new table linking my existing tables would just be a copy of my 'one' table...
    It doesn't feel right, but I think I got it working using a DAX pattern from https://www.daxpatterns.com/handling...granularities/
    Screengrab of revised results: https://ibb.co/jTqn6e
    I think the ISFILTERED kind of means "involved in the pivot table"
    Anyways, I think this is the solution but I'd love to hear any comments or corrections. Thanks again for looking at this.

  6. #6
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,776
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: One to many: every "many" displaying for every "one"

    yes if you want the budget total to show up on the subtotals only then this is the way to go and because the pivo table will automatically not display blank rows then you do not need to denormalize anymore
    Excel and BI blog: http://xcelanz.com/

Some videos you may like

User Tag List

Tags for this Thread

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
  •