topic Re: Distinct Count Two Measures in DAX Commands and Tips

Distinct Count Two Measures

91asma2 — Wed, 23 Jun 2021 19:22:21 GMT

Re: Distinct Count Two Measures

Anonymous — Wed, 14 Apr 2021 21:01:37 GMT

... 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

Re: Distinct Count Two Measures

91asma2 — Thu, 15 Apr 2021 20:11:10 GMT

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?

Re: Distinct Count Two Measures

Anonymous — Thu, 15 Apr 2021 21:35:45 GMT

91asma2

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...

Re: Distinct Count Two Measures

91asma2 — Wed, 23 Jun 2021 19:22:04 GMT

Hi daxer,

Yes, my model is a star schema.

Re: Distinct Count Two Measures

Anonymous — Thu, 15 Apr 2021 22:33:54 GMT

91asma2

Do you have a separate dimension for your users?

Re: Distinct Count Two Measures

Anonymous — Thu, 15 Apr 2021 22:45:14 GMT

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

Re: Distinct Count Two Measures

91asma2 — Thu, 15 Apr 2021 22:59:02 GMT

This works! Thanks a lot Anonymous !

and yes I do have a separate dimension for users.

Re: Distinct Count Two Measures

Anonymous — Thu, 15 Apr 2021 23:15:28 GMT

Can you please mark the answer as the solution? This helps me and others who will be looking for something smilar... Thanks.