Calc Field - How to apply one information on a line item to the same group

There are multiple entities in each group, and some of them have tags. I have a set of data that is under different groups. If the entity is part of the group, the group level should be tagged the same as the entity level, is there a way to write a calc field that applies entity level tagging to the group

Hi @qiao First of all, welcome to the QuickSight community :slight_smile:

Could you please share an anonymised sample of input data as well as what you’re expecting to achieve as an output? That would help forum members answer your question… Thanks

1 Like

Hi @qiao

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question.

@SD_QS @Max For example, for a given entity A, it can have update 3 line items a, b, c. And only Line item a include tagging information ‘X’. Is there a way to apply that ‘X’ to all line items under same entity? Expected output is something like below:

Entity Line Item Tagging
A a X
A b X
A c X
B e Y
B f Y
C a X

1 Like

@qiao Here is the calculated field that I created:

Output = firstValue(Item,[Entity ASC],[Entity])

This is of course just an example, and you should modify the above formula as needed…

Output below:

Let us know if this does the trick for you? Thanks.

It is not always first value. E.g. it can be line item b or c with Item tagging “X”, is there a way to do something like partition by Entity to apply the Item to output?

@qiao Tagging not being the first value for a line item within a specific entity should be alright, as long as it is the only value within an entity that you need across all rows… You should be able to play around with the “Sort” attribute to fit your needs… Also, the third parameter in the function is the partition by attribute… Doc reference: firstValue - Amazon QuickSight

So I would recommend you to try it out and let us know if you run into any issues… Thanks

PS: apologies for the error in the column headers in the screenshot, please read second column as Line Item and third column as Tagging

When Im trying to use this formula, it keep returning below error message. Is there a reason that might lead to it? When I test with different formula, it worked

cc: @Max @duncan

Hello @qiao !

I have seen that issue come up and has usually been solved by a hard reset of the browser. Were you able to get past the error is it persisting?

Hi @duncan after restart its still persist

It now showing a different error that the new field is not addable to the dimension with error message " **Custom aggregations can’t be converted to a dimension"

Since im utilizing same function, is there a reason why your example allows drop this field to a table?

I dont have it in the Group by field well; if you look at the screenshot the cal field is in the Value section

got it, this is helpful, thank you!