Need something similar to COUNTIF() across 2 unrelated tables in PowerBI

[El_Ha]

Hello,

Relatively new BI user here, I am struggling to make a small table in Power BI which will allow me to count the number of times each value in 1 table appears in another table.  My data appears as follows:

I have Referral table, which I created in the query editor. I created it using an mquery and converted it from a list into a table.

This just contains a list of all referral options that can appear on records (records can contain 1 or more referrals). 

The empty column here is where I would like to get a count for the number of times each of these options appears in the main table.

Secondly I have my main table with all the records extracted from the database(salesforce). 

Originally the Referral__c .1/2 columns were 1 column which had to be split as it was from a multi select picklist, and so values would appear in 1 line separated by a semicolon.

The table above has a basic filter which just removes any records that don't have referrals.

I am thinking this should be something relatively easy to do but I cannot seem to find a solution, I have tried to use Calculate and count functions and I have tried to create a relationship between the 2 tables so that I could use related function (this was not possible as the option is just greyed out).

I cannot seem to get some DAX to show how many times each of these options appears in the main table, or in 2 of the columns in the main table (Referral__c.1 and Referral__c.2)   A measure would also be fine for this I think?  I just need to be able to match the options in from my Referrals table to the number of times it is occuring in main table.

Any help would be greatly appreciated.

Thanks,

El

[AllisonKennedy]

@El_Ha
I also agree with @vivran22 that you should split the Referral column into rows. You can edit this in the Query Editor by clicking the settings cog next to the Split Columns by delimiter step and then follow the screenshot from @vivran22

I like that you have a referral table, but this is more useful if you have other information about each referral (such as Type, Location, start date, etc). As @vivran22 mentioned, you will need to make sure there is a 1 to many relationship between the Referral table and the data table.

To get the 'table' you want, you can use the Matrix visual and put:
Referral[Referral] in the Rows
Table[ID] in Values and set that to Count (or Count Distinct if you want how many unique ideas rather than how many total times it appears).

[vivran22 · Accepted solution]

Hello @El_Ha ,

From what I have understood, I have recreated the scenario below:

Sample tables:

Relationship

Formula:

For splitting the column, I would recommend that use the following option in Power Query:

This way, you will have your all the results in one column.

Hope this helps.

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[El_Ha]

Thats awesome! Thank you both so much 😊