Need something similar to COUNTIF() across 2 unrelated tables in PowerBI
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
