How do I pass dates from a page-level filter to a measure?

[Anonymous]

I have a memberships table:

membership_id	membership_start	membership_end	user_id
1	01-01-2021	31-12-2021	a
2	01-06-2021	31-12-2021	b
3	01-01-2022	31-12-2022	c
4	01-01-2023	31-12-2023	d

And a users table

user_id	account_created	city
a	01-02-2020	Austin
b	02-02-2021	Austin
c	03-03-2021	Seattle
d	04-04-2020	Chicago

I want to create a bar chart consisting of "count of active users, by city" in a given date range. I want to be able to change this date range as a page-level filter. (Multiple charts should get affected if I change this date range)
Lets assume the date range is date_range_start to date_range_end

I would consider a user as active if:

1. membership has started before the date_range_end, and ends after the date_range_start

(membership_start <= date_range_end) && (membership_end >= date_range_start)

 OR
2. account_created is between the date range

(account_created > date_range_start) && (account_created < date_range_end)

So If I select the date range as (06-06-2020 to 06-06-2021), I would get a joined table like this, along with a measure calculated with above conditions:

membership_id	user_id	membership_start	membership_end	account_created	city	is_active
1	a	01-01-2021	31-12-2021	01-02-2020	Austin	TRUE (satisfies 1st cond.)
2	b	01-06-2021	31-12-2021	02-02-2021	Austin	TRUE (satisfies 1st cond.)
3	c	01-01-2022	31-12-2022	03-03-2021	Seattle	TRUE (satisfies 2nd)
4	d	01-01-2023	31-12-2023	04-04-2020	Chicago	FALSE

I was able to do this by creating a measure that satisfies above conditions. But I am not able to add a page-level filter on the measure is_active to choose only the rows where is_active = TRUE.

I need the resulting table to be:

membership_id	user_id	membership_start	membership_end	account_created	city	is_active
1	a	01-01-2021	31-12-2021	01-02-2020	Austin	TRUE (satisfies 1st cond.)
2	b	01-06-2021	31-12-2021	02-02-2021	Austin	TRUE (satisfies 1st cond.)
3	c	01-01-2022	31-12-2022	03-03-2021	Seattle	TRUE (satisfies 2nd)

So that I would create a bar chart using "city" on x-axis, and "count of city" on y-axis, for the selected date range. (Of course, I should be able to change the date-range, which in turn changes the charts)

Is there a way to get the resulting table like above, while getting the date-range as user input?

[Anonymous · Accepted solution]

Hi @Anonymous ,

I made simple samples and you can check the results below:

Measure = var _max = MAX('Table 2'[Date])
var _min = MIN('Table 2'[Date])
RETURN IF(MAX('Table'[membership_start       ])<=_max&&MAX('Table'[membership_end      ])>=_min,1,IF(MAX('Table'[account_created       ])>_min&&MAX('Table'[account_created       ])<_max,2,0))

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[lbendlin]

Not really a need for that as it is part of the filter context that the measure operates in.  If you must, use VALUES()  or FILTERS().