Recently I faced an interesting scenario where I need to replace values in a comma-separated string using values from a lookup table. It looked like a simple task at first but I had to do some research to achieve the required result. So thought of sharing the approach I took.
Scenario
I was building a report which pulls data from a SharePoint online list which stores information about online task management. Each task entry will have a Description, Task Start Date, Task End Date, Id of resources, the task is assigned to etc. Resource information is stored in another SharePoint list. Lets bring the data into Power BI.
Task Table has information about Tasks including a column ‘ResourcesAssigned’ which contains Id of resources the Task is assigned to.
Resources Table has information about Resources like Name, Department, etc.
Now we need to join the Tasks table and Resource table Using ResourceId so that we can get Name of the Resources from Resource table. Looks very simple correct? But if you look at the scenario closely, you will see that the ResourcesAssigned column in the Tasks table has comma-separated values and we won’t be able to join both the tables unless we find a way to separate each value in that column.
After analyzing multiple ways to arrive at the desired result, I decided to take below approach
- Split ResourceIds in to separate values
- Lookup in Resource table using these Ids and find the corresponding Name
- Combine back the Resources but use Names in place of Ids
I have added a Calculated Column and used below DAX expression
This will give us the desired values as below
Now let us look at the DAX expression and see how it works
[dax]
Resources Assigned =
VAR resourcelist =
SUBSTITUTE(Tasks[ResourcesAssigned], ";", "|")
VAR reslength =
PATHLENGTH(resourcelist)
VAR mytable =
ADDCOLUMNS(
GENERATESERIES(1, reslength),
"mylist", VALUE(PATHITEM(resourcelist, [Value]))
)
VAR mylist =
SELECTCOLUMNS(mytable, "list", [mylist])
Return
CONCATENATEX(CALCULATETABLE(Resources, Resources[ResourceId] in mylist),
CONCATENATE(Resources[FirstName] & " ", Resources[LastName]), "; ")
[/dax]
Here we are making use of the Parent-chield Hierarchies function in DAX. You can read more about them here.
At first, we are replacing ‘;’ with ‘|’ symbol so that we can use PATH functions and storing the replaced value in a variable
[dax]VAR resourcelist = SUBSTITUTE(Tasks[ResourcesAssigned], ";", "|")[/dax]
Now we will check how many Resources are assigned for that Task. We can use the PATHLENGTH DAX function for this purpose.
[dax]VAR reslength = PATHLENGTH(resourcelist)[/dax]
Now let us put these Resource IDs in a table. We will be using ADDCOLUMN function to generate the table and we will use PATHITEM to populate the table with Resource IDs.
[dax]VAR mytable =
ADDCOLUMNS(GENERATESERIES(1, reslength), "mylist", VALUE(PATHITEM(resourcelist, [Value]))
)[/dax]
Now let us
sdsfs
