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

[TheUnit]

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 subordi

nate row label to the "one" side records. I have an example model screengrabbed that I think makes the request very clear.
Thanks!

 

[VBA Geek]

I can-t see any screenshot but have a look at the RELATEDTABLE function in DAX

 

[TheUnit]

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!

 

[VBA Geek]

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.

 

[TheUnit]

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-different-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.

 

[VBA Geek]

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