CONCATENATE VALUES for Tooltip in a Matrix

[MURTAZA]

Hi All,
I have below dataset

City	Company	Employee ID	Department	Name
Calgary	Company-A	121	21230	BOB
Calgary	Company-A	122	21240	Allen
Calgary	Company-A	121	21250	BOB
London	Company-B	124	21260	John
London	Company-B	121	21270	Bob
London	Company-B	126	21280	Peter
London	Company-B	127	21290	Micheal
Boston	Company-C	128	21300	Francis
Boston	Company-C	126	21310	Peter
Boston	Company-C	130	21320	Richard
Boston	Company-C	131	21330	Josh

I want to create a matrix visual with City, Company and Employee ID in rows columns and then using selectedvalue all other data fields (like name in this case) in the values. However, there is no option to bypass the aggregation the matrix visual does. For example, Employee '121' is working in the same city and same company for 2 different departments, but in the matrix, it shows 121 only once, so what I did was Concatenated employee ID with department ID to create a unique key and all the data was being displayed.

In the Matrix Visual, I am unable to see all the details in the tooltip, so I created a custom tooltip. The tooltip should show all the departments each employee is working in, irrespective of city or company. For example, when I take my mouse on BOB, it should show me (21230, 21250, 21270).

When I was using Employee ID values, as the thrid input in the rows field, I was able to achieve this result by using the below DAX. However, since now I am using the unique ID (instead of employee ID), I am unable to get this result.

Tooltip =

CALCULATE(CONCATENATEX(

SUMMARIZE( Sheet1, Sheet1[Employee ID], Sheet1[Department] ),

Sheet1[Department] ,", "),

ALLEXCEPT(Sheet1,Sheet1[Employee ID]) )

I want to see the above result with Unique ID. Hope this makes it clear. I have also attached the .pbix file for your convenience. Thank you.
https://drive.google.com/file/d/1TUphEogImIWSO8XzekfBZcM98onwKfBR/view?usp=sharing

[negi007 · Accepted solution]

@MURTAZA I have tried to solve your problem. This is what I did 

1. Created a summarize table 

Summarize = SUMMARIZE(Sheet1,Sheet1[Name],Sheet1[Department])

2.  Linked two tabels on Name fields

3. Created a new tooltip that you can use. I have used matrix table instead of Card

4. Below is the output

I have attached pbix file here as well. let me know if it helps you. Happy to help

[MURTAZA]

Hi @negi007 
This solved my problem. Thank you so much!

[lbendlin]

"So it should show "Percentage = 40%,60%", when I hover over the first row and in the second row "Percentage = 100%"."

It is not clear to me what the logic behind this is. Please explain in more detail.

[MURTAZA]

Hi @lbendlin 

Thanks for reaching out. I have changed my dataset and post to make it more clear. Can you please review again my post above?
I have also attached .pbix file. Thank you.