Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Extract Certain Users

Hello Experts, I have some user reports, in some cases the system assigns a chain of people for tracking, however I only need to assign a person the closure of said event to follow up, and I would not be finding a way to do it, the users I want to leave listed in a column is only one of the lab users currently highlighted in the users column.

Greetings and thank you very much.

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    Please refer to sample file with the solution https://www.dropbox.com/t/8vsnZ9m7YnzmWJDy

    Final User = 
VAR QualifiedUsers = VALUES ( 'Follow Up'[User Failure Follow Up] )
VAR CurrentUsers = Failures[User]
VAR ConvertToPath = SUBSTITUTE ( CurrentUsers, ", ", "|" )
VAR T1 = GENERATESERIES ( 1, PATHLENGTH ( ConvertToPath ), 1 )
VAR T2 = ADDCOLUMNS ( T1, "@User", PATHITEM ( ConvertToPath, [Value] ) )
VAR AllUsers = SELECTCOLUMNS ( T2, "User", [@User] )
VAR T3 = INTERSECT ( AllUsers, QualifiedUsers )
VAR NumOfQualifiedUsers = COUNTROWS ( T3 )
VAR FinalUsers = CONCATENATEX ( T3, [User], "|" )
VAR FinalUser = PATHITEM ( FinalUsers, 1 )
RETURN
    FinalUser

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Admin, here you can find the edit data with the expected results.

    Failure                User

    1122Luciano Martinez
    1123Camilo Sexto, Alfonzo Hernandez, Pedro Lopez, Martin Fiero, Lautaro Fernandez
    1234Martin Fiero
    2432Camilo Sexto, Lautaro Fernandez, Pedro Lopez
    2488Alfonzo Hernandez
    2892Pedro Lopez, Alfonzo Hernandez, Martin Fiero, Lautaro Fernandez, Camilo Sexto
    3296Lautaro Fernandez
    3700Alfonzo Hernandez
    4104Leonardo Castillo
    4509Juan Castro

     

    User Failure Follow Up

    Juan Castro
    Luciano Martinez
    Martin Fiero
    Lautaro Fernandez
    Leonardo Castillo
    Leonardo Semprum
    Alfonzo Hernandez
    Carlos Ruiz
    Estefania Solis
    Julio Estrada

     

    Failure             User

     Final Data Example
    1122Luciano Martinez
    1123Alfonzo Hernandez
    1234Martin Fiero
    2432 Lautaro Fernandez
    2488Alfonzo Hernandez
    2892 Martin Fiero
    3296Lautaro Fernandez
    3700Alfonzo Hernandez
    4104Leonardo Castillo
    4509Juan Castro

     

     

    Please let me know if that is clear.

    Regards

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Syndicate_Admin 

    would you please request @ArgenisSuarez to advise the the expected results and to provide copy/paste version of the sample data?

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 
    Please refer to sample file with the solution https://www.dropbox.com/t/8vsnZ9m7YnzmWJDy

    Final User = 
VAR QualifiedUsers = VALUES ( 'Follow Up'[User Failure Follow Up] )
VAR CurrentUsers = Failures[User]
VAR ConvertToPath = SUBSTITUTE ( CurrentUsers, ", ", "|" )
VAR T1 = GENERATESERIES ( 1, PATHLENGTH ( ConvertToPath ), 1 )
VAR T2 = ADDCOLUMNS ( T1, "@User", PATHITEM ( ConvertToPath, [Value] ) )
VAR AllUsers = SELECTCOLUMNS ( T2, "User", [@User] )
VAR T3 = INTERSECT ( AllUsers, QualifiedUsers )
VAR NumOfQualifiedUsers = COUNTROWS ( T3 )
VAR FinalUsers = CONCATENATEX ( T3, [User], "|" )
VAR FinalUser = PATHITEM ( FinalUsers, 1 )
RETURN
    FinalUser