Question

Filtering with AND condition

  • 15 April 2021
  • 1 reply
  • 29 views

Hi,

Let’s have a look at this table:

 

 

In the explore we have:

Dimension: Product

Measure: Number Of Customers (count distinct customer ID)

Measure: Number Of Orders (count disitinct order ID)

 

The question I want to find an answer is:

How many customers ordered both Guitar and Ocarina across all orders? Answer is 1 (customer A)

What would be the best way to model it? I understand that in this explore it is not possible to do that, or am I wrong?


1 reply

If you could have the Customer ID as a dimension, then build a table which groups by the Customer ID, pivots on the Products and uses Number of Orders as the measure. 

Create a calculated column that evaluates the value of each pivot column so that if total > 0 in each pivot column then it would evaluate to true and that Customer ID would have ordered all products in the table.

You then could filter on any combination of products and get the customers who ordered all the filtered products.

Reply