topic Re: group by row count in DAX Commands and Tips

group by row count

Anonymous — Wed, 26 Jun 2019 22:30:13 GMT

I have one column that referes to submissions.. I want to count all submissions in a certain group and provide in a matrix eACH TIME SOMEONE SUBMITS A FORM.. IT DETERMINES THE GROUP SELECTED AND ADDS TO THE GROUP Type

I tried to use the follwing DAX ... sorry I am not that proficient with DAX yet.

CALCULATE(COUNTA(Table1[Submissions]),FILTER(Table1,[Submissions]="Open"),FILTER(Table1,[Submissions]="Submitted"),Table1[Type])

Group A 10

Group B 12

Group C 20

Group D 100

Group E 0

Re: group by row count

Anonymous — Thu, 27 Jun 2019 09:39:27 GMT

If you want to obtain an answer, please formulate your question clearly and give enough detailed information so that people can understand your problem. Thanks.

Re: group by row count

Anonymous — Thu, 27 Jun 2019 12:23:07 GMT

Sorry,

thought I did

I have 2 different tables that are joined together on Department

I want to count the number of people Submitting application to a certain departmentfrom the joined table and group the count by Department Type

Table 1 Table 2

Applicant Department

Joe HR Department # Employees # Applicants (Table 1)

Jim Marketing HR 30 3

John Marketing Marketing 22 2

Heather HR Bus Dev 7 1

Jack Bus Dev

Tay HR

Department and # of employees can be calculated from Table 2... How would I calculated (count and group by Department from Table 1(Applicants)

Re: group by row count

Anonymous — Thu, 27 Jun 2019 13:15:49 GMT

Hi there.

Say you have 2 tables:

* Applicants, with columns: Applicant (this does not need to be unique since you can have one person apply for many departments as much as I understand) and Department (+ any other columns). Column Department should be hidden and slicing by Department should be done through Departments[Department].

* Departments, with columns: Department (should be unique) (+ any other columns)

There is a relationship: Departments 1:* Applicatnts on Department.

You probably want this measure:

[# Applicants] := DISTINCTCOUNT( Applicants[Applicant] )

When you then slice by Departments[Department] it will give you the count of applicants in each department.

Best

Darek