Can two columns that contain similar data be visualized together?

[beard194]

Good afternoon everyone, 

I did my best to search the forum to find a solution to my issue before posting, but I was unsuccessful. Here is an example of what I am trying to do:

- my data source is an online SharePoint list 

- the two columns I'm trying to visualize together are "Employee #1 work location" and "Employee #2 work location" 

- in the SharePoint list these columns have 13 identical options for work locations 

Sample Data: 

Case - Employee #1 work location - Employee #2 work location

1       - New York                             - Boston

2       - Florida                                 - New York

3       - Ohio                                    - Boston

I am trying to create a visual that combines the Count of these two values, so for example I want to show:

New York - 2

Boston - 2

Ohio - 1

Am I missing something obvious or easy? Right now the best solution I can come up with is to use the card visual for each indivudual count, with no ability to add them up. 

Thank you in advance for your help and expertise! 

-beard

[christinepayton · Accepted solution]

What I would do here is reference or duplicate the query, remove all but the SharePoint ID and the work location columns, and then unpivot the work location columns so that they are only in one column. You can use the former column titles as the label to differentiate between location 1 and location 2. 

Then relate the two tables on the SharePoint item ID, and it becomes a dimension that you can count. 

Someone else might have a clever DAX solution for this, but unpivoting would be my go-to.

[beard194]

Thanks for your response, I don't have any experience with unpivoting but I'll do some research and give it a shot!