Counting text records from three columns and presenting sum values in a visualisation

[Anonymous]

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

[AlB · Accepted 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

[Anonymous]

Thank you @AlB for the straightforward and elegant solution. Much appriciated.