Total for other players being displayed when it should not appear

[ompowerbi]

Hi, 

Fairly simple question perhaps:

I have a tables like this:

Table1:

Table2:

For table1, the row with a blank player is showing the sum of scores for all other players from table2. I have got the scores in the first table by creating a relationship with the second table based on the player column. There is no blank row in table1 and don't know where this comes from. I know I can filter out the blank row easily and solve the issue, but I wanted to try and make a leaderboard for each team and this is impacting me doing that. Any suggestions on why this is happening?
Thanks!

[v-stephen-msft · Accepted solution]

Hi @ompowerbi ,

I tried to reproduce your problem.

This is because the Player column in Table 1 is not equivalent to the Player column in Table 2. In other words, because the Player value in Table 2 does not exist in the Player in Table 1.

You could try to create a measure to solve it.

Measure = CALCULATE(SUM('Table 2'[Score]),FILTER('Table 2',[Player]=MAX('Table 1'[Player])))

Best Regards,

Stephen Tao

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

[some_bih]

Hi @ompowerbi the best practice would be to not have blank rows; to get it your table 1 and table 2 should be the same for key columns, meaning rows in two tables should be the same for connected columns. The rults of calculation later depending on your model, could be affected this blank rows. Go to your model and adapt changes in Table 1 (as table 2 have more rows). Hope this help

[ompowerbi]

Hi @some_bih, apologies if I am misunderstanding, but there are no blank rows in table 1 and table 2. I don't understand why this blank row is appearing, can you suggets why please? How would you model this data instead?

[some_bih]

Hi @ompowerbi Power BI show blank rows as in two tables there are some rows in one table which are not in second table. In your case probably in Table2 there are some players which are not in Table1. Check this with distinctcount and decide what to do next. Hope this help

[ompowerbi]

The players in table1 are indeed a subset of the players in table2, is there a way to stop this blank row cropping up, it is affecting the sum of table1 in other calculations?