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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors