Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to identify contacts that have multiple roles where one role type = Professor and one role type = Student. I have included some pictures of my sample data and relationships below.
Link to sample data:
https://www.dropbox.com/s/1tgcuxrn2rtso6x/test.xlsx?dl=0
I think this needs to be a calculated column because I need row by row data not aggregate but I am not sure how to link multiple rows of related data in a formula. Below is what I have tried so far.
Column = VAR RoleType = Role[Role Type] Return IF( RoleType = "Professor" && RoleType = "Student", "True", "False" )
I've also created a measure that counts roles and works at the contact level
Role Count = CALCULATE( COUNTROWS(Role))
Any help is greatly appreciated!
Solved! Go to Solution.
I'm sure there are lots of ways to do this. If you want a table ..
Table = INTERSECT( SUMMARIZECOLUMNS ( 'Role Data'[Contact.Full Name] , FILTER('Role Data', 'Role Data'[Role Type] = "Professor")), SUMMARIZECOLUMNS ( 'Role Data'[Contact.Full Name] , FILTER('Role Data', 'Role Data'[Role Type] = "Student")))
I'm sure there are lots of ways to do this. If you want a table ..
Table = INTERSECT( SUMMARIZECOLUMNS ( 'Role Data'[Contact.Full Name] , FILTER('Role Data', 'Role Data'[Role Type] = "Professor")), SUMMARIZECOLUMNS ( 'Role Data'[Contact.Full Name] , FILTER('Role Data', 'Role Data'[Role Type] = "Student")))
Thanks @HotChilli ! Just tried this out and it works like a charm. I was also able to create two measures to count the roles per contact that worked when I displayed it data in a visual table.
Professor Role Count = CALCULATE( COUNTX(Role, Role[Tole Type]), FILTER(Role, Role[Tole Type] = "Professor")) Student Role Count = CALCULATE( COUNTX(Role, Role[Tole Type]), FILTER(Role, Role[Tole Type] = "Student"))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |