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 ID Value XY $1 AB $2 CZ $3   Activities Status ID Complete AB In ...
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 😀
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 😀