Forum Discussion
Lookup Field
Yes, you can do this with CONCATENATEX() in DAX. I have a video tutorial describing various techniques for this sort of thing here if it helps - this is using a file source, not a SP list, but shows how to create the dimension table, relate them, then concatenate values with a comma between.
It looks like you expanded it into comma-separated values - I think that is the issue. I believe you probably need to expand to new rows to have it successfully expand the related values. You may need to duplicate the query as a dimension table and relate the two on ID if you don't want to expand it in your main table - this is a commonly-done thing with multivalue fields generally, not just with SharePoint sources.
After expanding the department ID and getting the department name, can I group them back together? For example John Doe is in department Administration and Com'l Service. I want it grouped where John Doe is only listed once with Administation, Com's Service in the same department name field.
Yes, you can do this with CONCATENATEX() in DAX. I have a video tutorial describing various techniques for this sort of thing here if it helps - this is using a file source, not a SP list, but shows how to create the dimension table, relate them, then concatenate values with a comma between.
Your video was extremely helpful, thank you so much!!