Aggregation options only allows Count/Count(Distinct) for text field

[Orbie]

Hi, hoping the community can help me here, I've done quite a lot of searching online but haven't been able to find anything related. Just posted this on the Power BI reddit but thought I would post here as well.

I have a Power BI report connected via Live Connection to a SSAS cube. Many of the fields are text data types. I have a map on my report where I am trying to put tooltips for the pushpins. However for some reason when I put my text fields in the tooltip I only get the option of "Count" or "Count(Distinct)" for aggregation, and thus instead of seeing text I see a number which is not what we want here. Yes there are multiple records being returned per pushpin location on the map, however we just want to return the First text value in the list for each attribute. The "First" and "Last" options though are not available to select.

We had previously rendered this report using an Import method and the options of "First" and "Last" were available to me when I was putting the text fields in the tooltip. Then all of a sudden when we switched to Live connection on SSAS (exact same query), the option is gone. I suspect it's a setting on the SSAS side but I'm not sure.

If anyone has any suggestions/ideas, it would be much appreciated!

[Nikola237]

Is it possible to use measure as Color saturation field?

[MFelix · Accepted solution]

Hi @Orbie,

Try to create a measure using FIRSTNONBLANK or LASTNONBLANK and then placing the result on your tooltip.

Regards
MFelix

[Orbie]

Hi @MFelix,

Thank you very much, this works great! I did try using measures before but I did not know about the FIRSTNONBLANK function so I wasn't able to filter to the first value properly which caused errors on my map when I tried to use that measure.

Do you know if the "First" and "Last" aggregation options are really just FIRSTNONBLANK and LASTNONBLANK functions behind the scenes? I was wondering if using these functions adds any further incremental performance issues over the out of the box "First" and "Last" filters.

Thanks again!