Counting a column based on a filter from another column

[TesterTest]

Hi Community,

I have two tables.

Table A:

Student #	Sibling #
1	S1
1	S2
2	S1
3	S1
3	S2
3	S3

TABLE B:

Student#	Sibling#	Status
1	S2	InActive
2	S1	Active
3	S2	Active

The two tables have been joined by Student# and Sibling # having many to many relationship.

(There are also other columns in the tables but have not been included for the sake of simplicity and they are not of my interest for this required calculation).

What I want is Count Distinct Student# in Table A where Status = Active.

I simply used a table visual, dragged Student# and selected Count(Distinct) for
Student#. Applied filterd 'Status' on the visual for 'Active'.

Expected result:

DistinctCount of Student#: 2

(As for Student# 1,there's only 1 sibling 'S2' in Table B which is Inactive. so this shouldn't be counted.

My Result:
DistinctCount of Student#: 3

In-fact, I want to get equivalent of the following sql to get my expected result:

select distinct count(Student#) from TABLEA a

inner join TABLEB b on

a.Student# = b.Student# and

a.Sibling# = b.Sibling#

where b.Status = 'Active'

(This query results count of 2)

I tried this DAX too

Studentcoutn = CALCULATE(COUNT('TABLEA'[STUDENTNUMBER]),FILTER('TABLEB','TABLEB'[STATUS]="Active"))

This is also not working.

Please advise.

Thanks in advance!

[rajendraongole1 · Accepted solution]

Hi @TesterTest -Create a measure to find the distinct students as below:

DistinctActiveStudents =
CALCULATE(
DISTINCTCOUNT('TableA'[Student #]),
FILTER(
'TableA',
COUNTROWS(
FILTER(
'TableB',
'TableA'[Student #] = 'TableB'[Student#] &&
'TableA'[Sibling #] = 'TableB'[Sibling#] &&
'TableB'[Status] = "Active"
)
) > 0
)
)

Replace TableA and TableB as per your model and add the above measure to your visual.

Hope it works.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[TesterTest]

This worked.

Really appriciate your help!

Thank you!!