Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Two Conditions for IF formula

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 bel...
Need Help
  • HotChilli's avatar
    HotChilli
    7 years ago

    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")))
HotChilli's avatar
HotChilli
Community Champion
7 years ago

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")))
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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"))