Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a login events table like that
where is event Id, user id and date.
I need to count active users and new users per selected period.
new user is the user who is in selected period and no in previous periods.
active user is user who is in seletced period and can be found in past periods
how can i check id user is in one period and another and oposite.
Solved! Go to Solution.
#All Usr = CALCULATE(DISTINCTCOUNT(events[user_id]),REMOVEFILTERS())
#New Usr =
COUNTROWS(
FILTER(
VALUES( events[user_id] ),
NOT events[user_id]
IN SELECTCOLUMNS(
FILTER( ALL( events ), events[event date] < MIN( Calendar[Date] ) ),
"@usr", events[user_id]
)
)
)
#Active Usr =
COUNTROWS(
FILTER(
VALUES( events[user_id] ),
events[user_id]
IN SELECTCOLUMNS(
FILTER( ALL( events ), events[event date] < MIN( Calendar[Date] ) ),
"@usr", events[user_id]
)
)
)
#Lost Usr =
CALCULATE(
DISTINCTCOUNT( events[user_id] ),
FILTER(
SELECTCOLUMNS(
FILTER( ALL( events ), events[event date] < MIN( Calendar[Date] ) ),
"@usr", events[user_id]
),
NOT [@usr] IN VALUES( events[user_id] )
),
ALL()
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
#All Usr = CALCULATE(DISTINCTCOUNT(events[user_id]),REMOVEFILTERS())
#New Usr =
COUNTROWS(
FILTER(
VALUES( events[user_id] ),
NOT events[user_id]
IN SELECTCOLUMNS(
FILTER( ALL( events ), events[event date] < MIN( Calendar[Date] ) ),
"@usr", events[user_id]
)
)
)
#Active Usr =
COUNTROWS(
FILTER(
VALUES( events[user_id] ),
events[user_id]
IN SELECTCOLUMNS(
FILTER( ALL( events ), events[event date] < MIN( Calendar[Date] ) ),
"@usr", events[user_id]
)
)
)
#Lost Usr =
CALCULATE(
DISTINCTCOUNT( events[user_id] ),
FILTER(
SELECTCOLUMNS(
FILTER( ALL( events ), events[event date] < MIN( Calendar[Date] ) ),
"@usr", events[user_id]
),
NOT [@usr] IN VALUES( events[user_id] )
),
ALL()
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
96 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |