Create custom columns by counting specific rows in different table based on multiple filters

Hi, Anonymous 

According to your description, I think you can use calculated columns in Power BI to achieve this requirement easily, you can follow my steps:

Create these calculated columns in the table ‘AllOpps’:

SalesContactRole =

var _count=

COUNTX(

    FILTER(

        RELATEDTABLE(AllOppsWithContactRoles),CONTAINSSTRING([ContactRole],"SalesContactRole")),

        'AllOppsWithContactRoles'[ContactRole])

return

IF(_count=BLANK(),0,_count)

TechContactRole =

var _count=

COUNTX(

    FILTER(

        RELATEDTABLE(AllOppsWithContactRoles),CONTAINSSTRING([ContactRole],"TechnicalContactRole")),

        'AllOppsWithContactRoles'[ContactRole])

return

IF(_count=BLANK(),0,_count)

OtherContactRole =

var _count=

COUNTX(

    FILTER(

        RELATEDTABLE(AllOppsWithContactRoles),CONTAINSSTRING([ContactRole],"OtherContactRole")),

        'AllOppsWithContactRoles'[ContactRole])

return

IF(_count=BLANK(),0,_count)

Then you can get what you want, like this:

You can go to the report page and create a table chart like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.