Calculate the number of active users depending on conditions

[Anonymous]

Hi all,

back to DAX after a while and I am a bit rusty. Can someone help me building a formula?

I have a calendar table I use as a slicer.

I have a table with the user data and with one record for each visit in the "Visit Date" column.

I would like to create a measure, so, when for example someone chooses "February 2022" in the slicer, it returns the number of active users for that time period (I will have other slicers like account name and so on.

An active user is considered who had at least one visit date for that given calendar month.

Would be possible to get help with this formula?

Thanks a lot!

[rohit_singh]

Hi @Anonymous 

Ensure that you have a relationship defined between your data table and the date table.

Once that is done, create a measure like below :

Active Users =

CALCULATE(

DISTINCTCOUNT(UserEmail[Account ID]),

ALLSELECTED(dim_date[MonthYear])

)

 This gives us

 

Please note that whatever field you use as the slicer for month must be used in the highlighted part in the measure. I have used the monthyear field in my example.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Anonymous]

Any reason why you use UserEmail[Account ID] instead of UserEmail[Email]?

[rohit_singh · Accepted solution]

No specific reason. I assumed there would be a 1:1 mapping between account ID and email based on the sample you provided. Using the email column should give you the same result.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Anonymous]

@rohit_singh unfortunately I can't share the original data because it is confidential but it looks something like this:

Email	Visit Date	Account ID
emaila@test.com	1/1/2022	ABCD
emaila@test.com	12/1/2022	ABCD
emaila@test.com	26/2/2022	ABCD
emailb@test2.com	15/1/2022	EFGH
emailb@test2.com	18/3/2022	EFGH

And so on...So in the example above in Jan I have 2 active users and in Feb and March 1 per month.

And I have a connection between the Visit Date and a Date Helper table like this:

[rohit_singh]

Hi @Anonymous ,

Please provide sample data in text format (not a screenshot) and the expected output.

Kind regards,

Rohit