Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Adding columns from related tables without filtering

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 1Level 1 stockLevel 2 Level 2 stock
A.1500A.2200
B.1300  

 

But this is how it is displayed:

Level 1 Level 1 StockLevel 2 Level 2 stock
A.1500A.2200
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.

 
 

 

 

 

Need Help

11 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        richbenmintz
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

        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

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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