Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 | Group | |
John Smith | j.smith@company.com | Engineering |
Jane Doe | j.doe@company.com | Marketing |
Bill James | b.james@company.com | HR |
The second table is...
Record | Opened | Closed | Updated |
1 | John Smith | Jane Doe | John Smith |
2 | Jane Doe | Bill James | Jane Doe |
3 | Bill James | John Smith | John 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.
Solved! Go to Solution.
@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.
@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.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |