Adding columns from related tables without filtering

[Anonymous]

Hi all,

I am trying to create a table visual which takes the stock of each sub assembly in a product, however, when I add the column for the next sub-assembly down, power BI removes the stock quantities for any assembly which doesn't have any more sub-assemblies. In this scenario, level 1 is the lowest sub assembly so whatever the highest level for a product, that will be the full assembly. 

For example: 

Level 1	Level 1 stock	Level 2	Level 2 stock
A.1	500	A.2	200
B.1	300

But this is how it is displayed:

Level 1	Level 1 Stock	Level 2	Level 2 stock
A.1	500	A.2	200
B.1

Some of the things I have already tried include:

Show items with no data - This will remove the row with B.1 in it completely 

If I just enter the Level 2 stock column without Level 2 then it appears to work

I have tried adjusting the relationships in the model (making them filter one way and both) but this doesnt change anything

A lot of tweaking the data... with no luck

Each of the columns comes from a separate table due to how the data has been handed to me, 

attatched is a screenshot of the relevant part of the model.

Thank you to anyone who is willing to give this a look.

[Anonymous]

I had the same problem and solved it by merging at the power query instead

[Greg_Deckler]

@Anonymous Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[amitchandak]

@Anonymous , Can you share a bit bigger sample data and sample output in table format?

[Anonymous]

Here are some screenshots from Power Bi which should show what is happening 

As you can see in the first line the number of 181 has been removed when it shouldnt be however the total remains the same

[richbenmintz]

Hi @Anonymous,

The easiest way for one of use to answer your question is to have a sample copy of your data and the expected output, you can mock it all up in excel and share the file, share a link to the pbix or plop the data here in a table with a screen grab of how the data should look in the end. 

Thanks,

Richard

[Anonymous]

This is how I want the data to look

I just dont understand why it is removing the values from the second column when I add the third one 

[richbenmintz]

Hi @Anonymous,

More than likely has to do with the way your data is setup and the relationships in your model, really hard to guess without having the model and seeing exactly where the columns are being pulled from the measures used and the way the data is related

[Anonymous]

I cant really post a file with any of the data on.

There are no measures being used here, it is just data from seperate tables

The model is connected in the picture that I put on the original post

[richbenmintz]

Hi @Anonymous,

If you cannot share the real data then please create a file with fake data and the exact structure of your real data, if you are not using measures then this is an issue with your data model. Really hard for me to help without a representative sample of the model you are using, unfortuneatly the image does not provide me with enough info.

Richard

[Ekacia]

Hello,

I hope this is what are you looking for?

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data