Calculated field that repeats a string

Hello guys, I need a lot of help, I have a table in Quicksight that shows message data from whatsapp conversations, I put them in context according to the image:

InitialPublishDate_CO: date per conversation
PublishDate_CO: date for each message in the conversation
CONVERSATION_ID: Number that identifies each conversation
MessageId: number that identifies each message
CC_FirtsAgent: First advisor to respond in chat with the client
Direction: type of message (inbound or outbound)
team_name: team name

I need a calculated field that repeats the Advisor’s name (CC_FirtsAgent field) in the empty spaces of each message.

In this query (link:How to replace empty fields) they gave me an option, but it doesn’t work for me, because I need the calculated field to be fixed, and this calculated field changes the name of the advisor with respect to the number of conversations that I add.

Note: it is important that the calculated field remains fixed and allows filters

It should be something like this (Red field):

You should be able to replicate this with a firstValue function.

In your case here is what your calculated field would look like

firstValue({CC_FirtsAgent},[ PublishDate_CO ASC],[{ CONVERSATION_ID}])

Note: the publishDate is only there to make it work but it doens’t really do anything unless there are two agents on the conversation. Then it will sort based on the first message.

Let me know if that works!

It doesn’t work for me :sob:, because that brings me the first value of the CC_firstAgent measure, you can see that the first measure of that field is empty (column E of image 2), I want to repeat that name, both for bottom and for top

Ahh. Can you replace the order by [ PublishDate_CO ASC] with [{ CC_FirtsAgent} DESC]

That should grab you the first non null value.

Hi @Jesus. Did Max’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!