Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have three columns in a table that are containing employee names.
EXAMPLE DATA | ||
Calls | Enquiries | Meetings |
Johan | Rachel | Mark |
Johan | Rachel | Mark |
Johan | Mark | Mark |
Johan | Johan | Rachel |
Johan | Rachel | Johan |
Mark | Mark | Johan |
Rachel | Mark | Rachel |
Mark | Mark | Rachel |
Is it possible to create a visualisation in Power Bi which would list all employees and count their appearance in those three columns? The real dataset contains 50+ names and 10k+ rows.
DESIRED OUTCOME | |||
Employee | Calls | Enquiries | Meetings |
Rachel | 1 | 3 | 3 |
Mark | 2 | 4 | 3 |
Johan | 5 | 1 | 2 |
It is easy to so with one column but I could not figure out how to do it with more columns. Could anybody please point me to the right direction?
All the best,
Verner
Solved! Go to Solution.
Hi @Anonymous
One of the options is to unpivot the columns.
Another is option is the following, where Table1 is the table you show
1. Create a one-column calculated table with the names of the employees:
Employees = SELECTCOLUMNS ( DISTINCT ( UNION ( ALL ( Table1[Calls] ); ALL ( Table1[Enquiries] ); ALL ( Table1[Meetings] ) ) ); "Name"; [Calls] )
2. Create these 3 measures:
MCalls = CALCULATE ( COUNT ( Table1[Calls] ); TREATAS ( DISTINCT ( Employees[Name] ); Table1[Calls] ) )
MEnquiries = CALCULATE ( COUNT ( Table1[Enquiries] ); TREATAS ( DISTINCT ( Employees[Name] ); Table1[Enquiries] ) )
MMeetings = CALCULATE ( COUNT ( Table1[Meetings] ); TREATAS ( DISTINCT ( Employees[Name] ); Table1[Meetings] ) )
3. Place Employees[Name] in the rows of a matrix visual and the three measures in values of the matrix
You can see it working here
Hi @Anonymous
One of the options is to unpivot the columns.
Another is option is the following, where Table1 is the table you show
1. Create a one-column calculated table with the names of the employees:
Employees = SELECTCOLUMNS ( DISTINCT ( UNION ( ALL ( Table1[Calls] ); ALL ( Table1[Enquiries] ); ALL ( Table1[Meetings] ) ) ); "Name"; [Calls] )
2. Create these 3 measures:
MCalls = CALCULATE ( COUNT ( Table1[Calls] ); TREATAS ( DISTINCT ( Employees[Name] ); Table1[Calls] ) )
MEnquiries = CALCULATE ( COUNT ( Table1[Enquiries] ); TREATAS ( DISTINCT ( Employees[Name] ); Table1[Enquiries] ) )
MMeetings = CALCULATE ( COUNT ( Table1[Meetings] ); TREATAS ( DISTINCT ( Employees[Name] ); Table1[Meetings] ) )
3. Place Employees[Name] in the rows of a matrix visual and the three measures in values of the matrix
You can see it working here
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |