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.
Here's what the measures should look like:
[Follow up] =
COUNTROWS(
FILTER(
VALUES( Assessment[User ID] ),
CALCULATE(
DISTINCTCOUNT( Assessment[assessment_id] ) >= 2
)
)
)
[Total Users Training] =
CALCULATE(
DISTINCTCOUNT( 'Sessions'[User ID] ),
KEEPFILTERS( 'Sessions'[Event Category] = "Training" )
)
[Users in Both] =
var FollowUpUsers =
FILTER(
VALUES( Assessment[User ID] ),
CALCULATE(
DISTINCTCOUNT( Assessment[assessment_id] ) >= 2
)
)
var TotalUsersTrainingUsers =
CALCULATETABLE(
DISTINCT( 'Sessions'[User ID] ),
KEEPFILTERS( 'Sessions'[Event Category] = "Training" )
)
var Result =
COUNTROWS(
INTERSECT(
FollowUpUsers,
TotalUsersTrainingUsers
)
)
RETURN
Result
Can you please mark the answer as the solution? This helps me and others who will be looking for something smilar... Thanks.
... which means your model is sub-optimal, to say the least. Your Users should exist in a dimensions of their own and be connected to both fact tables. Then it'll be DEAD EASY to do what you want. Please make your model correct first. If you want to know what it means you can read this:
Understand star schema and the importance for Power BI - Power BI | Microsoft Docs
Thank you, the article is helpful. But my model is correct and I know it easy but I am not sure how to approach it. What is your recommendation?
Your model might be correct but I really suspect it's sub-optimal. Is your model a star-schema with conformed dimensions?
By the way, your problem with that much information is not solvable right now. You have to show us the measures, (ideally) the model and what it means "to count the useres that exist in both measures." One can't write DAX in a vacuum...
Hi daxer,
Yes, my model is a star schema.
Here's what the measures should look like:
[Follow up] =
COUNTROWS(
FILTER(
VALUES( Assessment[User ID] ),
CALCULATE(
DISTINCTCOUNT( Assessment[assessment_id] ) >= 2
)
)
)
[Total Users Training] =
CALCULATE(
DISTINCTCOUNT( 'Sessions'[User ID] ),
KEEPFILTERS( 'Sessions'[Event Category] = "Training" )
)
[Users in Both] =
var FollowUpUsers =
FILTER(
VALUES( Assessment[User ID] ),
CALCULATE(
DISTINCTCOUNT( Assessment[assessment_id] ) >= 2
)
)
var TotalUsersTrainingUsers =
CALCULATETABLE(
DISTINCT( 'Sessions'[User ID] ),
KEEPFILTERS( 'Sessions'[Event Category] = "Training" )
)
var Result =
COUNTROWS(
INTERSECT(
FollowUpUsers,
TotalUsersTrainingUsers
)
)
RETURN
Result
This works! Thanks a lot @Anonymous !
and yes I do have a separate dimension for users.
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |