Forum Discussion
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 sheetreport sheet
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
Thanks ,
9 Replies
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
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 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.
Hello Anonymous ,
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 .
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:
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 .
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?