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

Join 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.

Reply
Lsh0576
Frequent Visitor

DAX Query Help!

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!

 

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.