getting unexpected data in table visual

[jmeyer348]

Hello! I’m new to power BI and I seem to be stuck.  I’m creating a table visual based on several related tables in my underlying data.  I’ll try my best to summarize the issue using dummy data below:

Table 1:

Sold to        ship to     amount

1234             1357           $524.90

1234              1357          $100.00

4312              2634          $1,154.00      

4312              2634          $1,000.00      

1234              4857           $678.00

1234              4857           $400.00

Table 2:

Sold To             company name                                          city

1234                  John’s example company               Jacksonville

4312                  George’s orange porridge               Seattle

6451                  Good & Gobble                                    Atlanta

Table 3:

Ship to             Company name                   City

1357                  Randall’s candles                San Francisco

2634                  Bushelbarrel’s                       Chicago

4857                  We pee                                      Tampa                

7531                   Storm Warmers                    Topeka

The “sold to” column in table 1 is linked in a many to 1 relationship with the “sold to” column in table 2.  The “ship to” column in table 1 is linked in a many to 1 relationship with the “ship to” column in table 3.

Some of the visuals that I created based on this data are showing the data I expect, and some are not.  For example, when I create a stacked bar chart with the x axis being ship to from table 3, the Y axis being the sum of the amount column from table 1, and put in the sold to from table 3 as a page filter,  I see the expected results.  When I filter down by sold to number “1234” in the filter, both ship to numbers 1357 and 4857 show up on the X axis.  This is expected because these are the two ship to numbers associated with sold to number 1234 in table 1.  The Y axis shows the sum of all the amounts for each of these particular ship to numbers.

However, when I display the same data in table format, I get different results.  Again, the page is filtered by sold to number 1234 from table 2 Below is an example of what I try to display this data in table format:

Ship to            company name                   city                           amount

1357                Randall’s Candles        San Francisco           $624.90

4857                We Pee                               Tampa                           $1,078

                                                                                                                  (blank)

2634                Bushelbarrel’s                 Chicago                       (blank)

7531                 Storm Warmers              Topeka                         (blank)

As you can see, I am getting my expected results in the top two columns.  But after that, I’m gettine one blank row followed by the ship to numbers that were on table 3 but not on table 1.  Since these were not on table 1, they show a blank in the amount column.   If anybody could clarify either of the following two questions for me, it would be greatly appreciated:

• How can I set up this table to only show the top two lines (the two ship to’s associated with sold to number 1234)?
• Can anyone explain why I get extra ship to values showing when this is displayed as a table, but not when it’s displayed as a stacked column chart?

[Anonymous · Accepted solution]

Hi @jmeyer348 ,

According to your description, I created the three tables and created relationships between them.

Then I put some fields in the table visual and used "sold to" of table 2 as a filter. It showed the same results of 4857 and 1357 as you described earlier.

Therefore, please carefully check the relationships between the tables to ensure that they accurately reflect the logical connection between the data. Also check the filters to ensure that the correct rules are used.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jmeyer348]

Thank you for taking a look at it! I think you're right, I also tried creating tables in power BI with the dummy data that I posted here, and I'm seeing the same thing as you, i get only the expected rows.  I can't figure out why I'm getting the extra rows when I use the actual data.  To me it looks like the same situation, just a bigger dataset.  I must be doing something wrong, but unfortunately I can't post the real data here.  Anyways I'm sure I'll get it eventually and I appreciate the response!