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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MURTAZA
Resolver I
Resolver I

CONCATENATE VALUES for Tooltip in a Matrix

Hi All,
I have below dataset

CityCompanyEmployee IDDepartmentName
CalgaryCompany-A12121230BOB
CalgaryCompany-A12221240Allen
CalgaryCompany-A12121250BOB
LondonCompany-B12421260John
LondonCompany-B12121270Bob
LondonCompany-B12621280Peter
LondonCompany-B12721290Micheal
BostonCompany-C12821300Francis
BostonCompany-C12621310Peter
BostonCompany-C13021320Richard
BostonCompany-C13121330Josh

 

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.

 

MURTAZA_0-1615088924371.png

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]) )

MURTAZA_1-1615089296956.png

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

1 ACCEPTED SOLUTION
negi007
Community Champion
Community Champion

@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
negi007_1-1615092865155.png

 

 

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

 

4. Below is the output

negi007_2-1615092928891.png

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

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

View solution in original post

4 REPLIES 4
negi007
Community Champion
Community Champion

@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
negi007_1-1615092865155.png

 

 

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

 

4. Below is the output

negi007_2-1615092928891.png

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

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

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

lbendlin
Super User
Super User

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

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors