DAX Query Help!

[Lsh0576]

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!

[Ashish_Mathur]

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.

[Greg_Deckler · Accepted 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

[Lsh0576]

This worked! Thank you for your help 🙂

[Lsh0576]

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!