Forum Discussion

rpiboy_1's avatar
rpiboy_1
Icon for Helper V rankHelper V
1 year ago
Solved

Measure - 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 c...
Need help!
  • rpiboy_1's avatar
    rpiboy_1
    1 year ago

    Solved my own problem....

        FILTER(
        SUMMARIZECOLUMNS(
            'User List'[id],
            "CountOwners", COUNTROWS(SUMMARIZE('Monthly User Activity by Project', 'Owners'[Owner Name]))
        ),
        [CountOwners] > 1
    )
     
Nasif_Azam's avatar
Nasif_Azam
Icon for Super User rankSuper User
1 year ago

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.