Count occurrences of Table1 Based on Criterion From Table2

[DonJulio]

Hello,

I am using Power BI Desktop (July 2018 version).

I am pulling data from 2 tables from SQL Server 2012.

They do NOT have a shared column.  It just happens that [Resouce].[Column1] 

may be part of the strings in [Resource_List].[Column1].

For simplication, I will use the following 2 tables as example.

Resource List

COLUMN1

A, B, C
C, D
E, F
A, F
B,C,F

Resource

COLUMN1
A
B
C
D
E
F

How can I add a column to the Resource table
as count of number occurances so I get the following?
This is doable in Excel with COUNTIF but how do I do this in PowerBI?

Resource | Count

COLUMN1 | COLUMN2
A                |       2
B                |       2
C                |       3
D               |       1
E                |       1
F                |       3

[DonJulio]

Anybody?

[Ashish_Mathur]

Hi,

You may download my solution from here.

Hope this helps.

[DonJulio]

@ashish

Thank you for the reply.  However, your solution is for Excel.

I need to do this in PowerBI.  And I am pulling data from 2 SQL Server tables.

[Ashish_Mathur]

Hi,

You simply need to go to PowerBI desktop > FIle > Import > Excel workbook Contents.

[DonJulio]

@Ashish_Mathur  Sorry if I didn't make this clear.  The above 2 tables are for example purposes.

I want to do this with PowerBI DAX.  

If I just want to upload a completed table I can easily do that myself.

Here's what I have so far, which isn't working

[DonJulio]

Anybody else?

[DonJulio · Accepted solution]

I managed to stitch together a solution.  The key is to use the FILTER function.

Count_Match = CALCULATE( COUNTROWS( [Resource_List]), FILTER([Resource],SEARCH('Resource'[COLUMN1], 'Resource_List'[COLUMN1], , 0) >0 ))