Filtering rows by measure on all of the pivoted values

explore
(Tim) #1

Hey there!

I have a question regarding filtering by measure in the explore section (applying an HAVING cause).

What I’d like to do is apply that filter to all of the pivoted values. Meaning that if one of the values doesn’t satisfy the result, then the row would be removed.

This is useful when you ask stuff like “Which users ordered at least 10 times per month in the last 3 months ?”.

Dimensions:
User

Pivot:
Month

Filters:
Month = Last 3 months
Orders >= 10

Typical result I get is:

User January February March
A 12 NULL NULL
B 12 14 11
C 12 16 NULL

What I want is:

User January February March
B 12 14 11

Meaning that if a user doesn’t order 10 times per month in the past 3 months, I don’t want it to appear in the results.

I was wondering if there was a way of doing this (via the query, hiding stuff via calculations or in the visualization …), without having to create a dedicated derived table.

I’m still new at this, if the right way of doing this is creating a derived table then … it’s fine! I just tend to avoid creating too many derived tables to keep the models as compact and maintainable as possible.

0 Likes

(Nicole Beyer) #2

Hey Tristan,

You can definitely do this using table calcs. I would suggest using an if function that outputs yes if the count of orders from your table is greater than 10 and no if not. Then you can utilize the hide nos from visualization option to hide rows that don’t meet those conditions.
I made a sample look on Learn to show this. You can check it out here.
(I sent you a login to Learn if you want to take a look!)

0 Likes

(Tim) #3

Hey Nicole!

Thanks a lot! This works perfectly!

0 Likes