cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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:

VAR CurrentDate = MAX('calendar'[Date])
VAR PaidUsers =
CALCULATE(
COUNTROWS('Paid Users - Master Data'),
ALL('calendar'),
'calendar'[Date]<=CurrentDate,
|| '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?
1 ACCEPTED SOLUTION
Super User

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,
|| 'Paid Users - Master Data'[LicenseExpirationDate]>=CurrentDate
)
return CALCULATE( [Active Users (NEW)], TREATAS( PaidUsers, Analytics_Visitors[Email]) )
2 REPLIES 2
Community Support

Hi @Anonymous ,

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.

Super User

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,
|| 'Paid Users - Master Data'[LicenseExpirationDate]>=CurrentDate
)
return CALCULATE( [Active Users (NEW)], TREATAS( PaidUsers, Analytics_Visitors[Email]) )

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors