Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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 DateEmailAccount ID
15/01/2022personab@gmail.comAB12345
16/01/2022personab@gmail.comAB12345
19/01/2022xyz@gmail.comXZ9876
25/02/2022xyz@gmail.comXZ9876

 

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 IDEmailPaid License Start DatePaid License End Date
AB12345personab@gmail.com1/1/202231/3/2022
XZ9876xyz@gmail.com12/1/202212/4/2022
QW567nzc@test.com1/1/202231/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
johnt75
Super User
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,
ISBLANK('Paid Users - Master Data'[LicenseExpirationDate])
|| 'Paid Users - Master Data'[LicenseExpirationDate]>=CurrentDate
)
return CALCULATE( [Active Users (NEW)], TREATAS( PaidUsers, Analytics_Visitors[Email]) )

View solution in original post

2 REPLIES 2
v-chenwuz-msft
Community Support
Community Support

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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