Identifying COVID Cases in the Map with Individual being in multiple locations

[naeus]

I hope I placed this in the right forum.

We need to identify the COVID cases in the building.  It is easier when a person is only assigned to one office.  Now my struggle is how to identify the person in the map having multiple location.

Would greatly appreciate what would be the best solution. Do I have to create different column  to separate them? I use the Synoptic Designer for Power Bi to create our own map using our building image.

	Room allocation
Staff 1	101, 210, 444
Staff 2	100
Staff 3	240, 300
Staff 4	235

[v-eqin-msft · Accepted solution]

Hi @naeus ,

You could transform the table by clicking "Split Columns" simlply.

 Or use the following formula to create a new table:

Table 2 =
VAR myvalues =
    ADDCOLUMNS ( 'Table', "RoomPaths", SUBSTITUTE ( [Room allocation], ",", "|" ) )
RETURN
    SELECTCOLUMNS (
        GENERATE (
            myvalues,
            ADDCOLUMNS (
                GENERATESERIES ( 1, PATHLENGTH ( [RoomPaths] ) ),
                "Room", PATHITEM ( [RoomPaths], [Value], INTEGER )
            )
        ),
        "Staff", [Staff],
        "Room Allocation", [Room]
    )

Here is the pbix file.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[naeus]

thanks! this is  just the one I am looking for...

[negi007]

@naeus I suggest you to create two seperate tables in your data 1) master data for location 2) transaction data for staff and their location. Below could be your tables

Location Master

Index	Location
1	1101
2	210
3	44
4	2100
5	3240
6	300
7	4235

Staff Transactions

Staff	Location
John	1101
John	210
John	444
Rebecca	2100
Rebecca	3240
Alex	300
Top	4235

one you create above two tables, then you can link them. On selection of an employee, all the location will appear for him on the visual

[naeus]

thanks!