cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
T_Thurston
New Member

Counting a value from one table that may appear in multiple columns in a different table.

This seems to be adjacent to many similar questions asked in other posts, but non of the answers have worked. 

 

I have two tables: one for names, emails, groups and the other for recorded activities. So table 1 looks like...

 

Name EmailGroup
John Smithj.smith@company.comEngineering
Jane Doej.doe@company.comMarketing
Bill Jamesb.james@company.comHR

 

The second table is...

 

RecordOpenedClosedUpdated
1John SmithJane DoeJohn Smith
2Jane DoeBill JamesJane Doe
3Bill JamesJohn SmithJohn Smith

 

What I would like to do is count the number of records that contain 'John Smith' in either the 'Opened', 'Closed', or 'Updated' column from table 2. So, in my dummy tables above if I look for "John Smith" in table 2 it should return a count of 2 since there are two records where John Smith appears in any one of the three text columns. 

 

I would like to avoid creating a column that contains all possible combinations of the user names. 

 

Thank you in advance for any assistance. 

1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

@T_Thurston you should unpivot your data and then it should be easy, to unpivot

 

-> tranform data -> select record column -> right-click -> Unpivot other columns

 

You will have two columns attribute and values, attribute will be opened/closed/updated and value will be the names

 

create relationship between 1st table and 2nd table on the name/value column and add following measure to get the count:

 

Count = DISTINCTCOUNT ( YourTable[Record] )

 

to visualize:

 

use name from the first column and above measure, you can also slice by any attribute.

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

1 REPLY 1
parry2k
Super User III
Super User III

@T_Thurston you should unpivot your data and then it should be easy, to unpivot

 

-> tranform data -> select record column -> right-click -> Unpivot other columns

 

You will have two columns attribute and values, attribute will be opened/closed/updated and value will be the names

 

create relationship between 1st table and 2nd table on the name/value column and add following measure to get the count:

 

Count = DISTINCTCOUNT ( YourTable[Record] )

 

to visualize:

 

use name from the first column and above measure, you can also slice by any attribute.

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors