Count total number with out the join

[maramassaf]

I have three tables 
Schools with the campus name
Teacher with the teacher name and location
Observation with the teacher name and campus. I am joining these three tables together to get the number of observation per teacher/campus/school. I am using slicer to filter for each value. Now I want to calculate the total number of teachers in each campus and school 
Ex. Campus 1 Boys School 20 teachers
Campus 2 Girls School 30 teachers and so on....
I am trying to do a distinctcount on teachers table but if the teacher is not in the observation table then it will not be counted because of the Join as it's returning only teachers in both table. 
How can i do that?

[123abc · Accepted solution]

If you want to count the total number of teachers in each campus and school, regardless of whether they have observations or not, you can use a combination of COUNTROWS and CALCULATE functions in DAX (Data Analysis Expressions). Assuming you have a relationship between the Teachers table and the Observations table, you can create a new measure to count the distinct number of teachers. Here's an example:

TotalTeachers =
CALCULATE(
COUNTROWS(
VALUES(Teachers[TeacherName])
),
ALL(Observations)
)

This measure uses the CALCULATE function to create a context transition and temporarily remove any filters applied to the Observations table. Then, it uses COUNTROWS and VALUES functions to count the distinct number of teachers in the Teachers table.

Now, you can use this measure in your report alongside the other columns to display the total number of teachers for each campus and school. For example:

TotalTeachersPerCampusSchool =
CALCULATE(
[TotalTeachers],
ALLEXCEPT(Observations, Schools[CampusName], Schools[SchoolName])
)

In this measure, ALLEXCEPT is used to remove all filters from the Observations table except for the ones related to CampusName and SchoolName from the Schools table. This way, the total number of teachers is calculated based on the selected campus and school.

Please adjust the table and column names in the DAX formulas based on your actual data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[123abc · Accepted solution]

I apologize for the confusion. You're right; ALLEXCEPT works within the same table. If you have separate tables for School, Teacher, and Observation, you might need to adjust the DAX expressions accordingly.

Assuming you have relationships set up between the tables (e.g., a relationship between Teacher and Observation on the teacher's name, and another relationship between Observation and School on the campus), you can try the following measures:

Total Teachers = COUNTROWS(VALUES(Teacher[TeacherName]))

Observations Per Teacher = COUNTROWS(Observation)

Observations Per Teacher Per School =
CALCULATE(
[Observations Per Teacher],
ALLEXCEPT(
Teacher,
Teacher[SchoolName]
)
)

Observations Per Teacher Per Campus =
CALCULATE(
[Observations Per Teacher],
ALLEXCEPT(
Teacher,
Teacher[CampusName]
)
)

In these measures, I've replaced 'Observation' with 'Teacher' in the ALLEXCEPT arguments, assuming that the relationships are established between Teacher and Observation as well as Teacher and School based on SchoolName and CampusName, respectively.

If your relationships are different or if you encounter issues, you may need to adapt the expressions based on your specific table relationships. Please make sure that relationships between tables are properly defined in your data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[maramassaf]

I am receiving an error that Allexcept needs to be within the same table, I can't use different tables in the argument

[123abc · Accepted solution]

I apologize for the confusion. You're right; ALLEXCEPT works within the same table. If you have separate tables for School, Teacher, and Observation, you might need to adjust the DAX expressions accordingly.

Assuming you have relationships set up between the tables (e.g., a relationship between Teacher and Observation on the teacher's name, and another relationship between Observation and School on the campus), you can try the following measures:

Total Teachers = COUNTROWS(VALUES(Teacher[TeacherName]))

Observations Per Teacher = COUNTROWS(Observation)

Observations Per Teacher Per School =
CALCULATE(
[Observations Per Teacher],
ALLEXCEPT(
Teacher,
Teacher[SchoolName]
)
)

Observations Per Teacher Per Campus =
CALCULATE(
[Observations Per Teacher],
ALLEXCEPT(
Teacher,
Teacher[CampusName]
)
)

In these measures, I've replaced 'Observation' with 'Teacher' in the ALLEXCEPT arguments, assuming that the relationships are established between Teacher and Observation as well as Teacher and School based on SchoolName and CampusName, respectively.

If your relationships are different or if you encounter issues, you may need to adapt the expressions based on your specific table relationships. Please make sure that relationships between tables are properly defined in your data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[123abc · Accepted solution]

If you want to count the total number of teachers in each campus and school, regardless of whether they have observations or not, you can use a combination of COUNTROWS and CALCULATE functions in DAX (Data Analysis Expressions). Assuming you have a relationship between the Teachers table and the Observations table, you can create a new measure to count the distinct number of teachers. Here's an example:

TotalTeachers =
CALCULATE(
COUNTROWS(
VALUES(Teachers[TeacherName])
),
ALL(Observations)
)

This measure uses the CALCULATE function to create a context transition and temporarily remove any filters applied to the Observations table. Then, it uses COUNTROWS and VALUES functions to count the distinct number of teachers in the Teachers table.

Now, you can use this measure in your report alongside the other columns to display the total number of teachers for each campus and school. For example:

TotalTeachersPerCampusSchool =
CALCULATE(
[TotalTeachers],
ALLEXCEPT(Observations, Schools[CampusName], Schools[SchoolName])
)

In this measure, ALLEXCEPT is used to remove all filters from the Observations table except for the ones related to CampusName and SchoolName from the Schools table. This way, the total number of teachers is calculated based on the selected campus and school.

Please adjust the table and column names in the DAX formulas based on your actual data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[123abc · Accepted solution]

Please accept it as a solution so that other community members could get benefit from it.

Very Thanks.

Regards:

Your Kudeod Ali Abbas