Power BI table visual shows (weird) numbers in column 2 when column 1 don't match?

[alya1]

Hi All,

In Power BI I have 2 tables linked by name, *:1. Table 1 is name, age, etc. and table 2 is name, hours, etc.

I made a Power BI table visual with ~10 columns, column 1 is Name from table 1 and column 2 is Hours from table 2.

The issue is, if there is a name that don't exist in table 2, the visual shows a random number in the hours column.

This number changes if I change the Hours column to sum or avg or count, this number and entire row dissapears if I select "Don't Summarize". 

Is it possible to show "not found" instead of a random number?

Does anyone know why/how this random number came to be? 

Thank you! 

[collinsg]

Good day alya1,

In your scenario the relationship is *:1, you then refer to table 1 and table 2 so I'm interpreting table 1 as being on the * side and table 2 on the 1 side. I'm interpreting also the filter direction is from table 2 to table 1 (as would be the normal direction for *:1). This interpretation may be wrong, in which case, ignore this reply.

If this is the scenario you intend then the hours column will sum, or average, or count the hours from all rows in table 2 for each row of table 1 and if you say "don't summarize", hours will be blank. This is because the first column creates a filter context but that context cannot flow from table 1 to table 2 because that would be against the direction of the filter. Therefore column 2 is not filtered in any way and therefore works on all rows containing hours.

Hope this helps