Measure - Intersecting counts of two dimensions

[rpiboy_1]

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!

[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:

1. Builds a virtual table showing each User and how many distinct Owners they have interacted with.
2. Filters that table to only include users with Owner count > 1.
3. 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 Azam

[v-hjannapu]

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

[rpiboy_1 · Accepted solution]

Solved 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!

[SamsonTruong]

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