Forum Discussion

Jeanxyz's avatar
Jeanxyz
Icon for Power Participant rankPower Participant
4 years ago
Solved

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's avatar
    SpartaBI
    Icon for Community Champion rankCommunity 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's avatar
    Jeanxyz
    Icon for Power Participant rankPower 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.