Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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! |
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |