Take data from one table - specific scenario

[gcardona77]

I need to basically create a matrix that basically looks at how often a person acted as a certain role. My roles are Salesperson, Manager, and Accountant. My source table has the company the person works for, and a column each for when they acted as a particular role. 

Here is an example:

Source Table

Record ID	Company	Salesperson	Manager	Accountant
1	ABC Company	Jon Smith	Jon Smith	Peter Jones
2	ABC Company	Peter Jones	Gracie Roberts	Ann Coulton
3	XYZ Company	Gabriel Martinez	Matthew DeNiro	Anne Peter
4	XYZ Company	Anne Peter	Gabriel Martinez	Gabriel Martinez

I need to show in a matrix or table the following:

Person	Company	As Salesperson	As Manager	As Accountant
Jon Smith	ABC Company	1	1	0
Peter Jones	ABC Company	1	0	1
Gracie Roberts	ABC Company	0	1	0
Ann Coulton	ABC Company	0	0	1
Gabriel Martinez	XYZ Company	1	1	1
Matthew DeNiro	XYZ Company	0	1	0
Anne Peter	XYZ Company	1	0	1

Any  help will be appreciated.

Thanks!

[dedelman_clng · Accepted solution]

Hi @gcardona77 - your solution is going to involve using Transform Data to unpivot the table.

Go into Transform Data (from the Home ribbon), highlight the last 3 columns and select "Unpivot Columns"

Your data will now look like this

And you can rename "Attribute" to "Role" and "Value" as "Person" (or Name, or whatever you'd like).

Now you can create a matrix without a single line of DAX code

Under "Value" it will be "Count" of "Record ID".

However, if you do want to write the DAX, now that you have the data unpivoted, you can write 3 measures with this pattern

As Salesperson = CALCULATE(COUNTROWS(Role), Role[Role]="Salesperson")

Place the measures in the matrix as values instead of having columns, and you will get your desired result.

Hope this helps

David

[gcardona77]

Perfect! Thanks!