Filter on first events in period

I have a requirement to filter a fact table by the first event for each user in a period.

e.g with user A B and C

1: user A - 02 jan 2024

2: user B - 04 jan 2024

3: user A - 05 jan 2024

4: user B - 01 feb 2024

5: user C - 02 feb 2024

If I filter on Jan 24, I want to return events 1 and 2 (excluding 3)

If I filter on Feb 24, I want to return events 4 and 5

If I filter on Year 24, I want to return events 1 2 and 5

My initial solution was to create a custom field on the fact table with that definition

Rank: rank() over (partition by "user" order by "event date") and filter on the field = 1

unfortunately, despite displaying the correct Rank, when it comes to filtering, the translation engine applies the filter on the field without applying the Event Date filter, meaning that it would always return event 1, 2 and 5.

Any other idea?

Dashboards & Reporting

Bruno
03-05-2025
Hi David,
It's possible that Assaf's multi-pass solution might work. However we have internally decided that the generated SQL would be too costly to run on a dashboard refresh so we refactored our solution.
Instead of ranking the events to identify the first in the requested period, we have pre-computed the "previous event date" at DB level and changed our filters to check that the "previous date" is before the requested period's "From date".
It is functionally equivalent and cheaper to compute at refresh time.

7 Replies

AssafHanina
Sisense Employee
02-26-2025
Hey Bruno ,

The Rank Function in the Elasticube isn't dynamic as it's fixed value after the Build is completed, Unlike Live Models which the result set is dynamic.

On the other hand, it's good to have it as it might provide the solution for the dashboard as in the dashboard will use the Rank Function as it require Numeric Value.

Note the solution shared is sample and might not work in all cases.

See an Example Dataset to examine the Use Case:

3 Users: A,B,C

Date Column For Each

Rank Function - Created as Custom Column in the Data Model

Date Filter on Month Granularity

Additional Calculated Column on the Dashboard Using Rank Function.

In the Example, Using the Rank Column which created in the Data Model to Apply Dynamic Rank in the Dashboard filter which will be calculated on the fly and group by the User.
Syntax: RANK([Total rank_asc],"ASC",[User])

While filter on 02/25 the rank calculation is Dynamic while in the data model the value is not changed. see example

Than added a widget filter on the Dynamic Rank Column to Set it for 1.

the Output in the dashboard

Additional Example Filtering on 03/2025

Best Regards
- Bruno
  Cloud Apps
  02-26-2025
  Thanks Assaf,
  
  Just to clarify, I'm using a live cube. I understand what you mean and I would not expect this to work with an Elasticube. However a live cube will also implement the Rank() in a subquery using the full table (i.e. before the time filtering), this is why my solution failed to retrieve the correct records.
  
  I tried implementing something along the lines of your suggestion, which works to retrieve the correct granular records IF/WHEN "User" is a queried dimension (otherwise it errors out due to a translation limitation, as far as I can see).
  
  Unfortunately my requirement is to run an average ( AVG(score)) over the whole set of events, so I'm back to square 1.
  - AssafHanina
    Sisense Employee
    03-04-2025
    Hey Bruno ,
    
    i understand, the rank of the date is by the user but the actual return should be the Score related to the date and the User present as indicator.
    
    It might be possible combining the Rank function with Multi Pass Aggregation
    
    The Sum function Support a Group By.
    
    DRay in case it's not resolving the issue it require additional review
    
    Best Regards
Jake_Raz
ETL
02-26-2025
Maybe try looking into the solution outlined here? Rather than creating a new column in the table for the Rank (which, as Assif pointed out, would result in static values instead of dynamic ones), you just use the "RANK" function in a value formula. In the link, they use MAX([date]) for the example, but you could switch it out for a MIN instead, or change the rank so it goes in descending order instead of ascending.
DRay
Admin
03-04-2025
Hello Bruno,

I’m following up to see if the solutiona offered by AssafHanina or Jake_Raz worked for you.

If so, please click the 'Accept as Solution' button on the appropriate post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.
- Bruno
  Cloud Apps
  03-05-2025
  Hi David,
  It's possible that Assaf's multi-pass solution might work. However we have internally decided that the generated SQL would be too costly to run on a dashboard refresh so we refactored our solution.
  Instead of ranking the events to identify the first in the requested period, we have pre-computed the "previous event date" at DB level and changed our filters to check that the "previous date" is before the requested period's "From date".
  It is functionally equivalent and cheaper to compute at refresh time.
  - DRay
    Admin
    03-05-2025
    Thank you for the update!