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!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors