Filtered rows showing up in matrix

[sdimond]

For whatever reason the database I am pulling data from inserts some duplicate row. These rows are easily identified and filtered out. The issue I am running into is these filtered rows are showing up in a Matrix.

The top row, without a year, shows the filtered rows that were filtered out in Power Query. On another page, I have cards that show totals and the total is the same as in the Matrix above. Why do the filtered rows still show in the Matrix and totals?  If I apply a "Filter in the Filters on all pages" I can filter out these extra rows. Is this the proper way to filter these rows from the reports? Is there a better way?

Thank you for any help.

[Anonymous · Accepted solution]

Hi @sdimond ,

When a relationship is invalid, the engine automatically creates an empty row in the table at one end of the relationship. Isolated rows in the fact table are pointed to this newly introduced empty row. Also note that this empty row will not exist in the table, it is created during data model loading. If the relationship becomes valid, this empty row will take effect again.
Refer to the following demo:

NumOfAllColors = COUNTROWS ( ALL ( 'Product'[Color] ) )
NumOfDistinctColors = COUNTROWS ( DISTINCT( 'Product'[Color] ) )
NumOfColors = COUNTROWS ( VALUES ( 'Product'[Color] ) )

The values function treats empty rows as valid, while the distinct function does not return empty rows

Best Regards,
Adamk Kong

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

[Ashish_Mathur · Accepted solution]

Hi,

It just means that there are some balnk dates in your fact table.  Go to the Table view and ensure that there are no blanks in the date column.

[Anonymous · Accepted solution]

Hi @sdimond ,

When a relationship is invalid, the engine automatically creates an empty row in the table at one end of the relationship. Isolated rows in the fact table are pointed to this newly introduced empty row. Also note that this empty row will not exist in the table, it is created during data model loading. If the relationship becomes valid, this empty row will take effect again.
Refer to the following demo:

NumOfAllColors = COUNTROWS ( ALL ( 'Product'[Color] ) )
NumOfDistinctColors = COUNTROWS ( DISTINCT( 'Product'[Color] ) )
NumOfColors = COUNTROWS ( VALUES ( 'Product'[Color] ) )

The values function treats empty rows as valid, while the distinct function does not return empty rows

Best Regards,
Adamk Kong

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

[ray_aramburo]

If they appear there it means that they were not filtered in Power Query or that you still had blank/null values after you applied the filter step. And yes, you can use the filter on all pages to remove them 🙂 

If they are going to be an annoyance to you, recommendation is to remove them completely in Power Query.

Also if the visuals have data from multiple tables, ensure your relationships are set up correctly.

[sdimond]

I may not have given enough information in my first post.

I have two tables in a one-to-many relationship. In the first table (one) I have a filter set up in Power Query to filter the duplicate entries. In this table, I get the number of Households. In the second table (many) I am getting the number of People, Adults, and Children. As far as I can tell the relationship between the two tables is set up correctly.

I get the correct number of Households, but I am not getting the correct number of People, Adults, or Children.

I am assuming the filter in Power Query on the first table will not affect the second table. If I apply a "Filter in the Filters on all pages" on the first table or use a slicer on the first table this will affect the second table. Is this correct?

[ray_aramburo]

Yes should work, but follow as well recommendations from @Ashish_Mathur  and @Anonymous  as that's what is likely causing you to have blank rows.