Forum Discussion

gcardona77's avatar
gcardona77
Regular Visitor
5 years ago
Solved

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 w...
  • dedelman_clng's avatar
    dedelman_clng
    5 years ago

    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

dedelman_clng's avatar
dedelman_clng
Community Champion
5 years ago

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