March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 (.....).
Solved! Go to Solution.
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 ,
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?
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 ,
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.
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
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 .
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
114 | |
76 | |
57 | |
52 | |
44 |
User | Count |
---|---|
165 | |
116 | |
63 | |
57 | |
50 |