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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
Super User

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

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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