Summarize Names from multiple Columns

[FelipeDelamar]

Hi Experts.

I have one table:

Project	Discipline 1	Discipline 4	Discipline 5	Discipline 6
Prj1	Mike	Carlos	Michael
Prj2	Charlie	Carlos		Max
Prj3	x	Matheus	Charlie	Jonas
Prj4	N/A	Cynthia		Mike
Prj5	Charlie
Prj6	Jasmine	Jonas	Charlie

I want to summarize it per discipline how many people are working on it.

What I desire is something like this:

Name	Discipline 1	Discipline 2	Discipline 3	Discipline 4
Mike	1	0	0	1
Charlie	1	0	2	0
x	1	0	0	0
N/A	1	0	0	0
Jasmine	1	0	0	0
Carlos	0	2	0	0
Matheus	0	1	0	0
Cynthia	0	1	0	0
Jonas	0	1	0	1
Michael	0	0	1	0
Max	0	0	0	1

I have tried with create table  = summarize, addcolumns.

I don't want to create tables at power query, want to do it with Dax.

Thank you.

[amitchandak]

@FelipeDelamar , Unpivot the data in power Query

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

Then you can create summarize with count or

And create a Pivot in visual using matrix and count

[FelipeDelamar]

Hey @amitchandak

As i follow your steps (unpivot and transpose).

I think it isn't right, how can i summarize with the data like this?

[amitchandak · Accepted solution]

@FelipeDelamar , Please find the attached file if that can help

[FelipeDelamar]

Great! Thank u, it was much more easy them I expected 😅