Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello there,
I was wondering if someone can assist me with a PowerBi query I am trying to do. I have two spreadsheets (Master Name) and (Transactions). I would like to do a calculation of the number of Master Name occurences in the Transactions table. Here is an example:
(Master Table)
Customer Name
Lisa Marie
Carlos
John
Luis
(Transactions Table)
Customer Name 1 Customer Name 2 Customer Name 3
Lisa Marie
Carlos Lisa Marie
Lisa Marie Lisa Marie
Luis
End Result I am looking for in my new column is:
Customer Name # of Occurences
Lisa Marie 4
Luis 1
Carlos 1
Can you please help me with writing this query in PowerBi?
Thank you so much!
Solved! Go to Solution.
@Lsh0576 Might be better if you unpivoted your columns in your second table but you can use the concept from MC Aggregations for this: Multi-Column Aggregations (MC Aggregations) - Microsoft Power BI Community
Should go something like:
# of Occurences Measure =
VAR __Column1 = SELECTCOLUMNS('Transactions',"Customer Name", [Customer Name 1])
VAR __Column2 = SELECTCOLUMNS('Transactions',"Customer Name", [Customer Name 2])
VAR __Column3 = SELECTCOLUMNS('Transactions',"Customer Name", [Customer Name 3])
VAR __Table = UNION( __Column1, __Column2, __Column3 )
VAR __MasterNames = SELECTCOLUMNS( 'Master',"Customer Name", [Customer Name] )
VAR __Count = COUNTROWS( FILTER( __Table, [Customer Name] IN __MasterNames) )
RETURN
__Count
Hi,
After unpivoting the second table, create a relationship (Many to One and Single) from the second table to the first one. To your visual, drag the names from the first table. Write this measure
Count = countrows('Table2')
Hope this helps.
@Lsh0576 Might be better if you unpivoted your columns in your second table but you can use the concept from MC Aggregations for this: Multi-Column Aggregations (MC Aggregations) - Microsoft Power BI Community
Should go something like:
# of Occurences Measure =
VAR __Column1 = SELECTCOLUMNS('Transactions',"Customer Name", [Customer Name 1])
VAR __Column2 = SELECTCOLUMNS('Transactions',"Customer Name", [Customer Name 2])
VAR __Column3 = SELECTCOLUMNS('Transactions',"Customer Name", [Customer Name 3])
VAR __Table = UNION( __Column1, __Column2, __Column3 )
VAR __MasterNames = SELECTCOLUMNS( 'Master',"Customer Name", [Customer Name] )
VAR __Count = COUNTROWS( FILTER( __Table, [Customer Name] IN __MasterNames) )
RETURN
__Count
This worked! Thank you for your help 🙂
I updated the query with the correct fieldnames and it ran successfully. I will qc the data and get back to you. I appreciate the assistance!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
56 | |
56 | |
38 | |
29 |
User | Count |
---|---|
75 | |
62 | |
45 | |
40 | |
40 |