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
LandOfSmile178
Frequent Visitor

How to count the specific text from another table?

Hello all,

 

Thank you for your time of reading my post. Currently, I'm building a measure to count the specific text from another table like in Excel when using formular COUNTIF(range,"*"&row&"*") in the example below but I don't know how to write measure in power BI so I hope you can help me for the solution. Thank you so much.

 

Scenario: In the excel file below, I have one sheet "product" and one sheet "report", you can see that I use the count formular with specific text to count because when extracting report from server, it will include the suffix after the product name (.....).

product sheetproduct sheetreport sheetreport sheet

1 ACCEPTED SOLUTION
bhelou
Responsive Resident
Responsive Resident

Hello , 

1: make a relationship between the 2 tables :  table 1 should be unique as ( product should not be duplicated ) , table 2 it should have Product column ( this can me duplicated as much as you want )  , the relationship shoul go 1 to many 

2 : in report view just make a table and drop the product from the first table in the columns ( dont sumarize it ) , and product from the table 2 and set it as count 

this is an aproach to make a table 
second approach : 

in table 1 that is unique make a new calculated column as per the image attached : 
count = countrows(relatedtable(table 2 )) 

Please accept as a solution if it works with you and a kudo is appreciated . 
* share some sample file if you didnt figure it out 

product.pngcount.pngcount rows.pngtale 2.png
Thanks , 


View solution in original post

9 REPLIES 9
LandOfSmile178
Frequent Visitor

Hi @bhelou , thank you for your reply. I tried in power query but the split is not what I want. What I want is just split the Product A, B, C and D into each row:

split.JPG

I can't find the attached file button so below is my table data:

 

Product
Product A(from USA)
Product C(from UAE)
Product A(from USA) Product B(from Japan)
Product C(from UAE) Product D(from Germany)
Product A(from USA) Product B(from Japan) Product D(from Germany)
Product A(from USA) Product B(from Japan) Product C(from UAE)
Product A(from USA)
Product C(from UAE)
Product A(from USA)
Product C(from UAE)
Product D(from Germany)
Product D(from Germany)

Dear Attached Image for the final resulet  also attached PBIX file see the steps how to achieve your target .

prodcut type.png


https://drive.google.com/file/d/1OoAFeSam3SNQ6cEUW4AWmmJC7DQo5AgA/view?usp=sharing 


Kindly accept as a solution and a kudo would be greatfull . 

Thanks , 

Thank you @bhelou , I will try later on my personal laptop since google drive is forbidden with our policy. Just quick question: do I have to re-do again all these steps in power query if the data is updated with many records in future?

Hello , 

Its will work  automatik everytime there is a refresh from the system . 

for any inquiries kindly contact me through my email 
bassem.7elou@gmail.com

Thanks , 

thank you @bhelou , I have sent you an email for inquiries.

bhelou
Responsive Resident
Responsive Resident

Hello , 

1: make a relationship between the 2 tables :  table 1 should be unique as ( product should not be duplicated ) , table 2 it should have Product column ( this can me duplicated as much as you want )  , the relationship shoul go 1 to many 

2 : in report view just make a table and drop the product from the first table in the columns ( dont sumarize it ) , and product from the table 2 and set it as count 

this is an aproach to make a table 
second approach : 

in table 1 that is unique make a new calculated column as per the image attached : 
count = countrows(relatedtable(table 2 )) 

Please accept as a solution if it works with you and a kudo is appreciated . 
* share some sample file if you didnt figure it out 

product.pngcount.pngcount rows.pngtale 2.png
Thanks , 


Hello @bhelou , thank you for your reply. I got your point now but our report system when extracting data always shown 2 or maybe 5 products in one row as image below. 

report sheetreport sheet

So is there any function that we can split them to one row? Like this

row1: product A product B product C

 

to 

row1: product A

row2: product B

row3: product C

 

Thank you.

Dear , 

Attached power Queries images how to clean data first

S1.pngS2.pngS3.pngS4.pngS5.png

Hello  @LandOfSmile178  ,

yes there is a way :

try to clean the data first from power queries ( split the data by space or delimiter ) , 

kindly share some sample data so we can achieve it , copy paste the data not an image here . 

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.