How do I filter by rows and count the total?

[ns1002]

I need to be able to filter on the columns below to create a new column that filters and counts based on them.

For example, if Type is "AWS" or "Azure" filter based on director and count the number of same title there are. Similary for Type that is not "AWS" or "Azure".

Type	VP	Director	Title
Azure	Steve	Adam	Engineer
Azure	Steve	Adam	Data Science
AWS	Steve	Adam	Engineer
AWS	Steve	Rory	Engineer
On-Prem	Steve	Rory	Network
On-Prem	Steve	Rory	Network
VHOST	Steve	Rory	Network
Azure	Mary	Austin	Data Science
Azure	Mary	Austin	Data Science

The new table with the column should look like this:

VP	Director	Title	Count
Steve	Adam	Engineer	2
Steve	Adam	Data Science	1
Steve	Rory	Engineer	1
Steve	Rory	Network	3
Mary	Austin	Data Science	1

I tried to use the DAX GROUPBY and also the GROUPBY in power query. I'm not able to figure out a solution.

Please help me, if you can.

Thank you!

[Anonymous]

Hi @ns1002 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[KRISHP1234 · Accepted solution]

Try this 
Table = SUMMARIZE(Sheet1,Sheet1[VP],Sheet1[Director],Sheet1[Title],"Counts",COUNTROWS(Sheet1))