DAX help... Count of distinct people except under a condition

[joyhackett]

I am hoping someone may know of a performant solution to my problem.

Sample data:

dim_user

user_id

1

fact_user_types

user_id	type	startdate	enddate	status
1	A	2/1/2022	1/1/2100	On Hold
1	B	3/2/2022	3/5/2022	Active
1	C	3/5/2022	1/1/2100	Active

Filters:

• Date Range
• Type

We are looking for the following:

1. Distinct count of user_ids that were Active at any point in the selected Date Range
2. Discount count of user_ids that were On Hold at any point in the selected Date Range
3. Table with distinct user_id and "true" status

Here's the kicker...

If a user_id has an On Hold record AND an Active record in the same date range, we need to exclude them from the On Hold count.  A user should never counted in both the Active and On Hold counts. Active will always trump On Hold.

Using the above example, these are the expected results for different scenarios:

Scenario 1:

Date Range 3/1/2022 - 3/31/2022, All Types

Active = 1   

On Hold = 0 * 

*even though the user has an on hold record, the fact that they also have an active record eliminates them from this count

Scenario 2:

Date Range 3/1/2022 - 3/31/2022, Type A Only

Active = 0

On Hold = 1

What I have so far are 2 measures for isActive and isOnHold. This gives me a value for each record. 

isActive =

VAR _MinDate = MIN(dim_date[full_date])

VAR _MaxDate = MAX(dim_date[full_date])

RETURN

   SWITCH (

      TRUE(),

              MIN(view_fact_user_membership_state[effective_from_date]) <= _MaxDate

              && MAX(view_fact_user_membership_state[effective_to_date]) >= _MinDate

              && MIN(view_fact_user_membership_state[current_membership_state]) = "Active"

      , 1, 0)

isOnHold is the same but with "On Hold"

So, when the Date Range is 3/1/2022 - 3/3/2022, I see this:

id	type	startdate	enddate	status	isActive	isOnHold
1	A	2/1/2022	1/1/2100	On Hold	0	1
1	B	3/2/2022	3/5/2022	Active	1	0
1	C	3/5/2022	1/1/2100	Active	0	0

What I'd like is 1 record per user_id:

id	status
1	Active

and 2 cards:

Thank you in advance for any assistance! 🙂 Joy

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[joyhackett]

This is great! Thank you so much. I see how you added a  column = {Number.From([startdate])..Number.From([enddate])} in the fact table. I'm a little concerned that it will make for a very very large sized table, but maybe it's better to store that data in memory rather than calculate the to/from on the report. I will experiment with this approach. Thanks again!!

[Ashish_Mathur]

You are welcome.

[joyhackett]

Oops - I c/p the actual dax for isActive instead of the simplified version with table/columns from my sample: 

isActive =

VAR _MinDate = MIN(dim_date[full_date])

VAR _MaxDate = MAX(dim_date[full_date])

RETURN

SWITCH (

TRUE(),

MIN(fact_user_types[startdate]) <= _MaxDate

&& MAX(fact_user_types[enddate]) >= _MinDate

&& MIN(fact_user_types[status]) = "Active"

, 1, 0)