Count Occurrence of Column Values in Another Table

[byr10112]

This sounds like a pretty simple request, but I havent been able to find a solution yet. Maybe I'm still thinking from a t-SQL perspective. 

I'm trying to find how many times a value in a column in my first table occurs in a column of a second table. Below are the tables:

Table A:

Skill Name

Math

French

History

English

Physics

Computer Science

Baseball

Table B:

Skills Requested

English

French

French

French

History

Basketball

Math

At the end of this, I would like Table A to look like this:

Skill Name	Occurence
Math	1
French	3
History	1
English	1
Physics	0
Computer Science	0
Baseball	0

So far, I tried a variety of columns and measures, but none of them worked.

I tried:

Occurence = COUNTROWS(FILTER('Table B', 'Table B'[Skills Requested] IN VALUES('Table A'[Skill Name])))

I also tried a variety of methods I found posted on this forum before, such as IF(HASONEVALUE, COUNTROWS) but nothing worked.

Any help would be appreciated.

Thanks,

Byr

[sevenhills · Accepted solution]

Measure = 
var _a = SELECTEDVALUE('Table A'[Skills Name])
var _b = HASONEVALUE('Table A'[Skills Name])
var _c = countrows( FILTER('Table B', 'Table B'[Skills Requested] = _a ))
Return 
if (_b, if (ISBLANK(_c), 0, _c), BLANK() )

Output:

[Anonymous]

Hey, thanks for this!
Is there a way to do the same but as a column? 

[meIdo]

How do I create occurence column instead of measure?

[byr10112]

Thanks for your reply. This measure returns the same number for every single entry. There are no blanks or 0s but there should be some. 

[sevenhills]

Not clear what you are saying ... what exactly you need?

I took your input tables and created measue you want in output table and matched it. 

[byr10112]

This measure returns 2663 for every single row in Table A, even if they do not exists in Table B. I don't know where 2663 is coming from. What you did makes sense to me but I don't know why it's not working in my report.  

[sevenhills]

Can you give more details to the context? so that it helps in answering. if the value is repeating on every row the same, gives me a doubt on which table the measure is added.

also, can you tell me, to which table you added this measure?

[byr10112]

I added the measure to Table A.

I have two tables that are indirectly connected to each other via a third table. One table has the list of users and their skills. The other table has the list of skills requested by the client. I'm trying to figure out how many times a skill a user has has been requested by the client. 

[sevenhills]

In the original/first post, 

Your requirement is simple one: one table is lookup/master table and other table is transactions/data table. You want to have a table of counts with or without data. Without data, you want to replace as zero's. so the measure I provided works for that scenario.

To get the answer, 

You have is three tables
a) Transaction/data table: Users + skills
b) Transaction/data table: Skills + Client
c) What is the third table? Not clear and how it is joined between two.

You should provide the data model diagram here .. when a third table exists, we may need to adjust accordingly. In order this one to work you need to share the exact model with sample data. 

[byr10112]

The third table is the user table. It basically has a list of the users and their information (not skills). Both tables are connected to the Users table via a user column.

Table A [User] --> Users [User]

Table B [User] --> Users [User]

[sevenhills]

Looks like you are having a many-to-many or non-unique values in Table A.

It is still not clear what the model looks like. I am suspecting you may need a bridge table or workaround table to contain unique skills. 

Read: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships 

Coming back , let us do this

a) Create a blank power bi file 

b) Create sample data for all the tables and paste the same data in this post. 

c) Create their relationships and share the picture, so that it helps 

Looks like your requirement is simple one, but I am not able to get it ...

[byr10112]

I looked at my relationships and found some mistakes. Once fixed, the emasure you provided works fine. Thanks for your help. 

[nickgesp]

Any chance you remember how to fixed this? I am getting the same issue where if the value is blank it puts in this big number, when it should be putting in 0.

[sevenhills]

Glad it all worked well.