Forum Discussion
How to extract distinct values from a column in a virtual table
Hi,
I have a table, I need create a calculated column which will look for Entity information per employee per date and create a text string which combines the entity property information for each employee in that month.
I have created a new column called Entity_combi, the problem is there are some duplicate values in the columns, e.g., the employee is assigned to two jobs in that month, both jobs sit in entity EMEA-BE, in this case, I want the value in Entity_combi to be the unique entity values, i.e., correct value shoudl be "EMEA-BE" instead of "EMEA-BE-EMEA-BE"
I can not use distinct () here because in my table, I have Index column which assigns a unique value to each line of data, I need to keep the index for follow up calculation. So I need some column calculation like the one below. This one doesn't work because I can't use a variable in Values() function.
Entity_combi =
var table_=filter(all(entity_summarize),entity_summarize[employee name]=earlier(entity_summarize[employee name])&& entity_summarize[Date Key]=earlier(entity_summarize[Date Key]))
var table2_=values(table_[Entity])
return
CONCATENATEX(table2_,entity_summarize[Entity],"-",entity_summarize[Index],ASC)
Thanks, SpartaBI,
I don't think it will work as the Concatenetex didn't sort by Index.
I figure out the solution later. Basically, I group the table by employee, entity, date key and extract max Index value for each group. then I use distinct() to remove duplicate lines in each group.
In this way, I removed duplicate entities, but it still keeps the index (max index) value for sorting purpose.
2 Replies
- SpartaBI
Community Champion
Hey, Jeanxyz , try this:
Entity_combi = VAR _employee = 'entity_summarize'[Employee Name] VAR _datekey = 'entity_summarize'[Date Key] VAR _table = FILTER( SUMMARIZE( ALL('entity_summarize'), 'entity_summarize'[Employee Name], 'entity_summarize'[Date key], 'entity_summarize'[Entity] ), 'entity_summarize'[Employee Name] = _employee && 'entity_summarize'[Date Key] = _datekey ) VAR _result = CONCATENATEX( _table, [Entity] & " " & 'entity_summarize'[Index], "-", [Entity], ASC ) RETURN _result - Jeanxyz
Power Participant
Thanks, SpartaBI,
I don't think it will work as the Concatenetex didn't sort by Index.
I figure out the solution later. Basically, I group the table by employee, entity, date key and extract max Index value for each group. then I use distinct() to remove duplicate lines in each group.
In this way, I removed duplicate entities, but it still keeps the index (max index) value for sorting purpose.