cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alex_
Frequent Visitor

Combining multiple strings into one cell

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

 

Data

IDValue
XY$1
AB$2
CZ$3

 

Activities

StatusID
CompleteAB
In ProgressXY
WaitingXY
CompleteAB
WaitingCZ
CompleteXY
CompleteCZ

 

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

IDValueStatus
XY$1In Progress
AB$2Complete
CZ$3Waiting

 

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?

1 ACCEPTED SOLUTION
jaideepnema
Solution Specialist
Solution Specialist

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:
jaideepnema_1-1614795047493.png

 

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 😀

View solution in original post

1 REPLY 1
jaideepnema
Solution Specialist
Solution Specialist

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:
jaideepnema_1-1614795047493.png

 

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 😀

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.