Forum Discussion
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!
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
4 Replies
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 __CountHi,
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.
