Show rows with no data when applying filter

In some cases, when exploring you apply a filter “not null” and it returns a column with a null/empty value that cannot be filtered out.

This is due to the behavior of applying both the “Filter” and “Show rows with no data” feature.

Let us explain how it works with the below example.

Examples

We have 3 models: Users, Orders and Products.

The relationship between them is: Users (1) - (N) Orders (N) - (1) Products.

Suppose that you want to know how many orders each user has belong to “Jeans” products, you will add 3 fields to your exploration:

• Dimension: D1: users.id, D2: products.name
• Measure: M1: count(orders.id)
• The condition here is:  products.name contains ”Jeans”

The actual result is the below image. In there, D1 has data, D2 and M1 have null data while you’ve already set the condition that D2 not null ( contains “Jeans”).

In your expected result, D2 should have data as the condition instead of showing null.

Why did this happen?

With normal exploration, Holistics only runs one query (1) with all dimensions, measures, and filters. So, the exploration won’t include dimension combination with “empty results” because “not null” filter removed the “no data” rows.

But when enabling the “Show rows with no data” feature, Holistics will run 2 more queries ((2) and (3)) to fetch the dimension combination with “empty results”. These queries only use the filter that is directly applied to them.

As you can see, we have 3 joined queries:

(1) to get users.id - products.name - count(orders.id)

(2) to get users.id - products.name

(3) to get users.id

And then a big join to gather results of (1), (2), (3).

Let's see how “Show rows with no data”+ “Filter” affects 3 queries:

• Query 1 will include all filters. Therefore, the results of Query 1 contain rows that have “Jeans” purchases and have no “empty results”.
• For Query 2, the filter "non-null" applies (products.name contains ”Jeans”), thus it clears out the null values that are generated from the "Show rows with no data". The filter is applied because this query includes D2 products.name, which is the field that contains the filter itself.
• For Query 3, the filter condition on D2 doesn’t affect D1. Therefore, it would include users that have not made any “Jeans” purchases.

To summarize: If both "Show rows with no data" + "Filter on Products" are enabled

• Empty rows on the products.name -related joins are cleared
• Empty rows on the users.id that don't have relevant purchases still appear. Therefore, the final result may still show users that have not made any “Jeans” purchases and have null product names although we’ve already had a "non-null" filter on products.name.

How to solve it?

Simply move the D2 on top (above the D1) or disable the Show rows with no data feature. In this case, when you apply the condition on D2, D2 should show the value as filtered instead of showing null.