Count the matching codes used in one table with other tables: Count the matching

[Anonymous]

Hi PowerBiers, I need you superior brain power!

I have a set of codes; Skills, Industries that are associated to People looking for jobs and those looking to hire people, as well as the job records and Organisations. 

Below three examples tables;

Company	Code Type	Code		Candidate	Code Type	Code		Job	Code Type	Code
BT	Skills	JAVA		John Smith	Skills	JAVA		Developer	Skills	JAVA
BT	Skills	.NET		John Smith	Skills	.NET		Developer	Skills	.NET
BT	Industry	Telecoms		John Smith	Industry	Telecoms		Developer	Industry	Telecoms
				John Smith	Skills	SQL		Developer	Skills	VBA

I need to represent in a table that totals these matches on codes and then show whether a sales/activity transaction has occurred. 

Candidate	Contacts/Contact with an action related to person	Jobs	Companies
John Smith	50/5	25/10	15/5
Joe Bloggs	45/40	10/0	5/5

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can refer to below steps to create the summary table if it suitable for your requirement:

Steps:

1. Summary three table and add the tag.

Summary= UNION(SELECTCOLUMNS(Sheet2,"Type","Company","Name",[Company],"Code",[Code Type]&" "&[Code]),SELECTCOLUMNS(Sheet3,"Type","Candidate","Name",[Candidate],"Code",[Code Type]&" "&[Code]),SELECTCOLUMNS(Sheet4,"Type","Job","Name",[Job],"Code",[Code Type]&" "&[Code]))

2. Use var function to store the above table, and add variables to log the table count.

Summary = 
var temp= UNION(SELECTCOLUMNS(Sheet2,"Type","Company","Name",[Company],"Code",[Code Type]&" "&[Code]),SELECTCOLUMNS(Sheet3,"Type","Candidate","Name",[Candidate],"Code",[Code Type]&" "&[Code]),SELECTCOLUMNS(Sheet4,"Type","Job","Name",[Job],"Code",[Code Type]&" "&[Code]))
var Contacts=COUNTROWS(FILTER(temp,[Type]="Candidate"))
var Company=COUNTROWS(FILTER(temp,[Type]="Company"))
var Job=COUNTROWS(FILTER(temp,[Type]="Job"))
return
temp

3. Summary temp table and calculate the count.

Summary = 
var temp= UNION(SELECTCOLUMNS(Sheet2,"Type","Company","Name",[Company],"Code",[Code Type]&" "&[Code]),SELECTCOLUMNS(Sheet3,"Type","Candidate","Name",[Candidate],"Code",[Code Type]&" "&[Code]),SELECTCOLUMNS(Sheet4,"Type","Job","Name",[Job],"Code",[Code Type]&" "&[Code]))
var Contacts=COUNTROWS(FILTER(temp,[Type]="Candidate"))
var Company=COUNTROWS(FILTER(temp,[Type]="Company"))
var Job=COUNTROWS(FILTER(temp,[Type]="Job"))
return
SUMMARIZE(FILTER(temp,[Type]="Candidate"),[Name],
    "Current Contacts/All",COUNTROWS(FILTER(temp,[Name]=EARLIER([Name])))&"/"&Contacts,
    "Suitable Job/All",COUNTROWS(INTERSECT(SELECTCOLUMNS(FILTER(temp,[Type]="Candidate"&&[Name]=EARLIER([Name])),"Code",[Code]), SELECTCOLUMNS(FILTER(temp,[Type]="Job"),"Code",[Code])))&"/"&Job,
    "Suitable Company/All",COUNTROWS(INTERSECT(SELECTCOLUMNS(FILTER(temp,[Type]="Candidate"&&[Name]=EARLIER([Name])),"Code",[Code]), SELECTCOLUMNS(FILTER(temp,[Type]="Company"),"Code",[Code])))&"/"&Company
    )

Notice: the company count means the suitable company records count.(not the unique value)

Regards,

Xiaoxin Sheng