Data filtering between tables problem

[CarlBlunck]

Hi there,

I have come across something weird that I would some feedback on.  The following model, allows a user that is selected from table 1 to see data in table number 4, even though they have no corresponding data in table number 3.  However, they can't see data in table number 5.

I don't understand how this is possible because based on the design, if there is no related in table number 3, then no data should appear in table 4.  Like it does for table 5.

Any ideas why data is still showing in table 4?

Cheers

Carl

[johnt75]

This could be to do with a blank row in table 3. Check that all values in both tables 2 and 4 have a corresponding value in table 3. If they don't, then a blank row will be added to table 3 automatically, and that will display the unmatched values in table 4.

[CarlBlunck]

@johnt75 this is exactly what is happening.  Is there anyway to get around this?

Thanks

Carl

[johnt75 · Accepted solution]

The only way to prevent it is to clean up the data. You need to make sure either that table 3 has all possible values which could appear in table 2 or 4, or that table 2 and 4 do not include values which are not in table 3.

Exactly how you do you this will depend on your specific case, but you could potentially do an inner join between table 3 and tables 2 and 4 to ensure that only valid values from table 3 appear in tables 2 and 4. Or append values from tables 2 and 4 into table 3 if they are not already there.

[v-jingzhan-msft]

Hi @CarlBlunck 

According to the screenshot of the relationships, table 3 is a dimension table.

Generally speaking, dimension tables should not have null or blank values. Dimension tables are used to describe the data in fact tables, and they contain all possible dimension values to ensure accurate grouping and filtering during data analysis. If there are null or blank values in a dimension table, it may lead to inaccurate analysis results. 

Therefore, it is recommended to ensure that the "PositionID" field (or the field that the relationship is created on) in the dimension table contain valid values when creating it. If a dimension value does not exist, you can use a default value (such as "Unknown" or "Other") to replace the null value. It also should have all possible "PositionID" values, which means those unmatched values in Fact table should exist in the dimension table. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!