Tooltip Showing Null Columns

[forti4040]

Hello All,

I've built a dashboard to audit how much data has or has not been populated in a Project Online site. I'm able to pull all the info and graph percentage of data populated. What I'd like to be able to do is have tooltip windows that show which columns are missing data when you hover over them. Does anyone know of a way to do this? 

Thanks in advance for any help

[v-cherch-msft]

Hi @forti4040

It seems you need to use 'Unpivot columns' in Query Edtior. Then you may create a measure to get the value as requested and drag the measure to 'Tooltip'. If it is not your case, please share some simplified data sample and expected output..

https://blogs.msdn.microsoft.com/charles_sterling/2016/10/09/displaying-text-columns-in-power-bi-too...

https://sqldusty.com/2016/06/29/5-more-power-bi-tips/

Regards,

Cherie

[forti4040]

Hello @v-cherch-msft,

Thanks for the quick response and links! I'm not completely following the suggestion for unpivot tables as I've not used that feature before (will look into it) but one of the links you sent feels like it gets me one step of the way there. Below is a simple version of the data and what I'm hoping to do with it. 

Conceptual Data:

Column A, Column B, Column C, Column D, Column E

--Task 1--, ---null---, -----Yes---,----null---,----No----

In the above scenario I'd like to have the tooltip show the headers for Column B & Column D so I know which fields were left blank. I have roughly 40 columns and different combinations of them will be left blank. So I can't hard code which column(s) to list. 

The below formula from one of the links you sent counts the items, but I'm wondering if I could adapt it to count which items are blank and concatenate the column headers of each of those columns into a new column? The tooltip could either show them as a single concatenated row of text, or it could show every blank column as it's own row in the tooltip. I'm open to either solution. 

Classes =
VAR ItemCount = DISTINCTCOUNT(‘Grade data'[Class Name])
RETURN
IF(ItemCount >= 3,
CALCULATE(CONCATENATEX(TOPN(3,VALUES(‘Grade data'[Class Name])),’Grade data'[Class Name],”, “))&” and more…”,
CALCULATE(CONCATENATEX(VALUES(‘Grade data'[Class Name]),’Grade data'[Class Name],”, “))
)

Thanks for the help!

Forti4040

[forti4040]

@v-cherch-msft,

I briefly looked at unpivot and now it makes sense why you've suggested that. I'll play around with the feature a bit today to see what I can mangage. Thank you again for the input!

Forti4040

[v-cherch-msft · Accepted solution]

Hi @forti4040

You may refer to below sample data and create a measure like:

Measure =
CONCATENATEX ( FILTER ( Table3, Table3[Value] = "" ), Table3[Attribute], "&" )

Regards,

Cherie

[forti4040]

@v-cherch-msft,

Thank you again for the great insights! I can certainly see where you are heading with this and it makes sense how it would work. Being new to the unpivot feature I am a bit confused as to how I can utilize it without impacting the rest of my report data. When I unpivot data it duplicates rows and messes up a bunch of the formulas. Is the intent that I would create a duplicate table that is somehow reading from the original and doing the unpivot action there? This way it keeps the original data intact but has an unpivoted section that I can pull from?

I feel like once I wrap my head fully around the unpivot issues you will have completely solved my issue!

Regards,

Forti4040

[forti4040]

@v-cherch-msft,

I did some more digging and figured out the rest. I created a reference table to my original data to work with and that was part of the trick. The other thing I found was that unpivoting actually removed the blanks from my data so I had to replace blanks with a value, unpivot, then replace the value with blanks again. But I've got it running now thanks to you!