DAX - Help with measure based on subgroup with a link to another table

[Anonymous]

Hi,

I am currently having a date table I use as slicer and I have:

1st Table

Table with all the visitors (one record for every day they visit it, therefore I have this DAX measure to calculate the Active Users:

Active Users (NEW) = CALCULATE(
DISTINCTCOUNT(Analytics_Visitors[Email]),
ALLSELECTED('calendar'[Month])
)

Visit Date	Email	Account ID
15/01/2022	personab@gmail.com	AB12345
16/01/2022	personab@gmail.com	AB12345
19/01/2022	xyz@gmail.com	XZ9876
25/02/2022	xyz@gmail.com	XZ9876

In this example the active user formula returns 2 for Jan and 1 for February (The active users are defined with at least 1 visit per month.

2nd Table
Table with the users being the paid license holders, each email has a start and end date for when they are a named paid user. And calculating the total like this:

Total Named License Holders =
VAR CurrentDate = MAX('calendar'[Date])
VAR PaidUsers =
CALCULATE(
COUNTROWS('Paid Users - Master Data'),
ALL('calendar'),
'calendar'[Date]<=CurrentDate,
ISBLANK('Paid Users - Master Data'[LicenseExpirationDate])
|| 'Paid Users - Master Data'[LicenseExpirationDate]>=CurrentDate
)
VAR Result =
IF(
YEAR(CurrentDate)<=YEAR(TODAY()),
PaidUsers
)
RETURN
Result

Data example:

Account ID	Email	Paid License Start Date	Paid License End Date
AB12345	personab@gmail.com	1/1/2022	31/3/2022
XZ9876	xyz@gmail.com	12/1/2022	12/4/2022
QW567	nzc@test.com	1/1/2022	31/12/2022

The 2 tables are joined using a mapping table as a bridge with Account ID.

What I am trying to get is a measure that tells me the amount of total Active Named Users.

Let's say I have 10 Named Licenses in February (10 emails in the second table), and only 5 of them are active for that month (5 of these emails in the first table of active users) , I would like the measure to return 5.

Can anybody help with that?

[v-chenwuz-msft]

Hi @Anonymous ,

Please try this code:

Make yourActive Named Users =
VAR active =
    CALCULATETABLE (
        VALUES ( Analytics_Visitors[Email] ),
        ALLSELECTED ( 'calendar'[Month] )
    )
VAR named =
    CALCULATETABLE (
        VALUES ( 'Paid Users - Master Data'[Email] ),
        FILTER (
            ALLSELECTED ( 'Paid Users - Master Data' ),
            [Paid License Start Date] >= MAX ( 'calendar'[Month] )
                && [Paid License End Date] >= MIN ( 'calendar'[Month] )
        )
    )
VAR active_and_named =
    INTERSECT ( active, named )
RETURN
    COUNTROWS ( active_and_named )

If you need more help, please share more example data.

Best Regards

Community Support Team _ chenwu zhu

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

[johnt75 · Accepted solution]

You could do something like

Active Named Users =
VAR CurrentDate = MAX('calendar'[Date])
VAR PaidUsers =
CALCULATETABLE(
VALUES('Paid Users - Master Data'[email]),
ALL('calendar'),
'calendar'[Date]<=CurrentDate,
ISBLANK('Paid Users - Master Data'[LicenseExpirationDate])
|| 'Paid Users - Master Data'[LicenseExpirationDate]>=CurrentDate
)
return CALCULATE( [Active Users (NEW)], TREATAS( PaidUsers, Analytics_Visitors[Email]) )