Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DonJulio
Frequent Visitor

Count occurrences of Table1 Based on Criterion From Table2

 

 

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

1 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 ))

View solution in original post

7 REPLIES 7
DonJulio
Frequent Visitor

Anybody?

Hi,

 

You may download my solution from here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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.

 

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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

Anybody else?

 

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 ))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.