Forum Discussion

Alex_'s avatar
Alex_
Frequent Visitor
5 years ago
Solved

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?

Need Help
  • jaideepnema's avatar
    jaideepnema
    5 years ago

    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 ğŸ˜€

1 Reply

  • jaideepnema's avatar
    jaideepnema
    Solution Sage
    5 years ago

    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 ğŸ˜€