Forum Discussion
Take data from one table - specific scenario
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!
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
2 Replies
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
Perfect! Thanks!
