Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |