Forum Discussion
Distinct Count Two Measures
- Anonymous5 years ago
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
... 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
- 91asma25 years agoHelper I
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?
- Anonymous5 years agoNot applicable
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...
- 91asma25 years agoHelper I
Hi daxer,
Yes, my model is a star schema.