Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
gcardona77
Regular Visitor

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 IDCompanySalespersonManager

Accountant

1ABC CompanyJon SmithJon Smith

Peter Jones

2ABC CompanyPeter JonesGracie Roberts

Ann Coulton

3XYZ CompanyGabriel MartinezMatthew DeNiro

Anne Peter

4XYZ CompanyAnne PeterGabriel Martinez

Gabriel Martinez

 

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

PersonCompanyAs SalespersonAs ManagerAs Accountant
Jon SmithABC Company110
Peter JonesABC Company101
Gracie RobertsABC Company010
Ann CoultonABC Company001
Gabriel MartinezXYZ Company111
Matthew DeNiroXYZ Company010
Anne PeterXYZ Company101

 

Any  help will be appreciated.

 

Thanks!

1 ACCEPTED SOLUTION
dedelman_clng
Community Champion
Community Champion

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"

 

2021-01-19 16_39_02-Qty Month - Power Query Editor.png Your data will now look like this

2021-01-19 16_40_22-Qty Month - Power Query Editor.png

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

 

2021-01-19 16_42_29-Qty Month - Power BI Desktop.png

 

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

View solution in original post

2 REPLIES 2
dedelman_clng
Community Champion
Community Champion

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"

 

2021-01-19 16_39_02-Qty Month - Power Query Editor.png Your data will now look like this

2021-01-19 16_40_22-Qty Month - Power Query Editor.png

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

 

2021-01-19 16_42_29-Qty Month - Power BI Desktop.png

 

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!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.