Combining multiple strings into one cell

[Alex_]

Hi! I have two sources of data like the following, in a one to many relationship using ID:

Data

ID	Value
XY	$1
AB	$2
CZ	$3

Activities

Status	ID
Complete	AB
In Progress	XY
Waiting	XY
Complete	AB
Waiting	CZ
Complete	XY
Complete	CZ

I would like to have a single value in Data for Status, so that In Progress will be shown if there is any to the corresponding ID, otherwise Waiting, and if neither then Complete, as follows:

Data

ID	Value	Status
XY	$1	In Progress
AB	$2	Complete
CZ	$3	Waiting

The goal is to display this in a table in the report, highlighting the progress of connected activities. There are 6 different status types (not started, etc). Essentially I want a column like this in Data, but don't know the formula:

IF 'Activities'[Status] CONTAINS "In Progress", "In Progress", IF Activities'[Status] CONTAINS "Waiting", "Waiting", IF 'Activities'[Status] CONTAINS "Complete", "Complete", ""

Is there a simple way to set this up?

[jaideepnema · Accepted solution]

Hi @Alex_ ,

Create a calculated column in Status Table:

Status Check = CALCULATE(CONCATENATEX('Status','Status'[Status],",",'Status'[Status],ASC),ALLEXCEPT('Status','Status'[ID]))

Create two calculated column in the data table :

Flag Check = LOOKUPVALUE('Status'[Status Check],'Status'[ID],Data[ID])

Value Status = 

Var Complete=SEARCH("Complete",Data[Flag Check],,-1)

Var InProgress=SEARCH("In Progress",Data[Flag Check],,-1)

Var Waiting=SEARCH("Waiting",Data[Flag Check],,-1)

var check=IF(InProgress<>-1,"In Progress",IF(Waiting<>-1,"Waiting",IF(Complete<>-1,"Complete",BLANK())))

return check

This will give you the desired result:

In case you need the file:

https://we.tl/t-yx9TmRbBd8

Hope this helps !!

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀