Count number of rows in different table by specific filters

[Anonymous]

Hi all,

I am quite new in powerbi and I am struggling with a task and I hope you can give me some good pointers.

I have two tables and I would like to add custom columns in table 1 for specific rowcounts (based on filters) I need to count in table 2.

Table1 - OpenOpps

- Unique list of opportunities - no duplicates

opp-id	opp-name	opp-value
1	abc	10000
2	xyz	5000
3	test1	7000
4	test2	8000

Table2 - OpenOppsWithContactTypes

- List of opportunities with assigned contact types (1 opportunity can have multiple contact types) -  duplicate columns with contact type as unique field

- The different contact types are for example SalesContactType1 / SalesContactType2 / TechnicalContactType1 / TechnicalContactType2 / ...

opp-id	opp-name	opp-value	ContactRole
1	abc	10000	SalesContactType1
1	abc	10000	TechnicalContactType1
2	xyz	5000	TechnicalContactType1
2	xyz	5000	TechnicalContactType2
3	test1	7000	SalesContactType1
3	test1	7000	SalesContactType2
3	test1	7000	SalesContactType3
4	test2	8000	OtherContactType1

Table1 is linked to table2 with a 1-many relationship on the field opp-id

What am I trying to achieve:

- Custom column "SalesContactType" to be added in table1 - count of all different SalesContactTypes for that specific opportunity

- Custom column "TechnicalContactType" to be added in table1 - count of all different TechnicalContactTypes for that specific opportunity

- Custom column "OtherContactType" to be added in table1 - count of all different OtherContactTypes for that specific opportunity

opp-id	opp-name	opp-value	SalesContactType	TechnicalContactType	OtherContactType1
1	abc	10000	1	1	0
2	xyz	5000	0	2	0
3	test1	7000	3	0	0
4	test2	8000	0	0	1

What I tried was something like this which provided me a number of all these entries in the table and not per opportunity. And it also only worked for one filter where I need to filter on multiple ones.

Column = COUNTROWS(FILTER('OpenOppsWithContactTypes', 'OpenOppsWithContactTypes'[Contact Role]="SalesContactType"))

Anyone has a good idea or suggestion?

Thanks.

DB

[Vera_33]

Hi @Anonymous 

Another way: Split the column in DAX, SalesContactType1 does not equal to SalesContactType...use the new columns to count rows

Type = LEFT([ContactRole],LEN([ContactRole])-1)

TypeNumber = RIGHT([ContactRole],1)

[Anonymous · Accepted solution]

Hello,

In power Query Editor, perform the "split column" By Non-Digit to Digit operation on the column "ContactRole" of Table2. then rename that column as "ContactRole".

Then create 3 measure like this in report,

SalesContactRole = CALCULATE(COUNT(AllOppsWithContactRoles[ContactRole]),FILTER(AllOppsWithContactRoles,(AllOppsWithContactRoles[ContactRole]="SalesContactRole")))+0
 

TechnicalContactRole = CALCULATE(COUNT(AllOppsWithContactRoles[ContactRole]),FILTER(AllOppsWithContactRoles,(AllOppsWithContactRoles[ContactRole]="TechnicalContactRole")))+0

OtherContactRole = CALCULATE(COUNT(AllOppsWithContactRoles[ContactRole]),FILTER(AllOppsWithContactRoles,(AllOppsWithContactRoles[ContactRole]="OtherContactRole")))+0

you will get the final result.

let me know if you need pbix file for the same.