Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 ))
Anybody?
Hi,
You may download my solution from here.
Hope this helps.
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.
@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 ))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |