Forum Discussion

bdusen's avatar
bdusen
New Member
1 year ago
Solved

Look Up Comma Delimited Values in a Cell

Hi all,   I've read dozens of other responses across these and other forums and I'm trying to figure out the best solution. I'm building reporting views off of massive data extract files that are p...
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    Do you want the output to be a calculated column or a measure?

    Also have you tried Fuzzy Merge in the Query editor?

     

    Assuming you split the Reviewer onto seperate rows then joined to the userid:

     

    Cross filter lets the Many side of the join filter the 1 side.

    So for each Item show the matching users.

     

    For the same outcome but using a calculated column without unpivoting:

    Apply a filter to the contacts table and test if the current row is within the current reviewer column.

nandic's avatar
nandic
Icon for Resident Rockstar rankResident Rockstar
1 year ago

Hi bdusen ,
Attached demo file based on your demo data.
Steps:
1) table Reviews, split user id column and then unpivot to get 2 columns: item id, user id
2) create relationship between Reviews and Contacts. Contacts will have 1 value of User Id, Reviews will have multiple
3) create additional column in dax in Reviews table: 

User Name = RELATED(Contacts[Name])
4) finally, create new table based on dax expression: 
Summarized data =
SUMMARIZE(Reviewer,Reviewer[Item ID],"Users",CONCATENATEX(VALUES(Reviewer[User Name]),Reviewer[User Name],"; "))

Yes, it is new table, but at least for start with something 🙂


Cheers,
Nemanja