Forum Discussion
Helper VMeasure - Intersecting counts of two dimensions
I have the following data model:
fact MonthlyUserActivity - each row represents the quantity of logins by a single user into a single project for each month
dim RegionalUsers - bridge table to consolidate user IDs
dim Users - list of all users
dim Projects - list of all projects
dim Databases - list of databases each project belongs to a single database
dim Owners - each database belongs to one owner
Relationships:
fact MonthlyUserActivity (*->1) RegionalUsers (*->1) Users
fact MonthlyUserActivity (*->1) Projects (*->1) Databases (*->1) Owners
What I'm trying to acheive is a Measure that:
- Calculates the quantity of Owners each User has interacted with by way of the Projects they access. I can represent this value in a Table or Matrix with this measure: COUNTROWS(SUMMARIZE('MonthlyUserActivity', 'Owners'[Owner Name]))
- Filters the resulting table to only rows where the COUNT is greater than 1
- Count the resulting rows to return the total number of users that have accessed data from multiple Owners.
I've tried a few things, but not having much luck in building a virtual table that returns the correct data. Suggestions are welcome!
Solved my own problem....
FILTER( SUMMARIZECOLUMNS( 'User List'[id], "CountOwners", COUNTROWS(SUMMARIZE('Monthly User Activity by Project', 'Owners'[Owner Name])) ), [CountOwners] > 1 )
5 Replies
- SamsonTruong
Super User
Hi rpiboy_1 ,
Can you try the following measure and let me know if this achieves your desired result?:Measure = VAR UsersWithOwnerCounts = ADDCOLUMNS( VALUES('Users'[User ID]), "OwnerCount", CALCULATE( DISTINCTCOUNT('Owners'[Owner Name]) ) ) VAR FilteredUsers = FILTER(UsersWithOwnerCounts, [OwnerCount] > 1) RETURN COUNTROWS(FilteredUsers)If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.
Thanks,Samson
rpiboy_1Solved my own problem....
FILTER( SUMMARIZECOLUMNS( 'User List'[id], "CountOwners", COUNTROWS(SUMMARIZE('Monthly User Activity by Project', 'Owners'[Owner Name])) ), [CountOwners] > 1 )- SamsonTruong
rpiboy_1 Awesome, great catch on the adjustment!
- Nasif_Azam
Hey rpiboy_1 ,
Thanks for the detailed explanation of your data model. You're on the right track you need a measure that:
- Builds a virtual table showing each User and how many distinct Owners they have interacted with.
- Filters that table to only include users with Owner count > 1.
- Counts the number of such users.
Users with Multiple Owners (Measure)
UsersWithMultipleOwners :=
VAR UsersOwners =
ADDCOLUMNS (
VALUES ( 'Users'[UserID] ),
"OwnerCount",
CALCULATE (
DISTINCTCOUNT ( 'Owners'[Owner Name] )
)
)
VAR FilteredUsers =
FILTER ( UsersOwners, [OwnerCount] > 1 )
RETURN
COUNTROWS ( FilteredUsers )If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif AzamHi rpiboy_1 ,
I would also take a moment to thank SamsonTruong , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please Accept it as a solution so that other community members can find it easily.
Thank you
Harshitha.
Community Support Team.
