Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

custom column to fetch values from multiple columns based on Id reference

Hi All,

 

I have a requirement to validate cell value (id), against multiple columns and return a calulated column saying the ID is part of which all applications. Something like below

 

Initial Data:

 

IDNameApp1App2App3
1Steve1  
2paul 2 
3adam33 
4smith444

 

Post Applying Custom Column:

IDNameApp1App2App3App Details
1Steve1  App1
2paul 2 App2
3adam33 App1,App2
4smith444App1,App2,App3

 

Please help on how to build this requirement.

 

Regards,

Sivapratap.

Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI Anonymous,

     

    Maybe you can try to use following calculate column formula:

    Detail=
CONCATENATEX (
    FILTER (
        {
            IF ( ISBLANK ( Table2[App1] ), "", "app1" ),
            IF ( ISBLANK ( Table2[App2] ), "", "app2" ),
            IF ( ISBLANK ( Table2[App3] ), "", "app3" )
        },
        [Value] <> BLANK ()
    ),
    [Value],
    ","
)

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI Anonymous,

     

    Maybe you can try to use following calculate column formula:

    Detail=
CONCATENATEX (
    FILTER (
        {
            IF ( ISBLANK ( Table2[App1] ), "", "app1" ),
            IF ( ISBLANK ( Table2[App2] ), "", "app2" ),
            IF ( ISBLANK ( Table2[App3] ), "", "app3" )
        },
        [Value] <> BLANK ()
    ),
    [Value],
    ","
)

    Regards,

    Xiaoxin Sheng