&&
1 Topic
Distinct count to find Attendance using multiple filters including AND
I am trying to total the number of days when a person is absent for both the am and the pm (on the same day) over a period of time based on multiple filters using a number of slicers. In essence Person# Date Period Abs Type 1 9/1/2019 am sick 1 9/1/2019 pm sick 2 9/1/2019 am sick 1 9/2/2019 am sick 1 9/3/2019 am personal 1 9/3/2019 pm personal 2 9/3/2019 pm personal 1 9/4/2019 am sick 1 9/5/2019 am sick 1 9/8/2019 am sick 1 9/8/2019 pm sick 2 9/8/2019 pm sick 2 9/9/2019 pm sick 2 9/10/2019 pm sick I can create a measure to count either the "am" or the "pm" but not both (aka 'and' or '&&'). For example, I can create a measure to count the morning absences. #absent = CALCULATE ( DISTINCTCOUNT ( BADetail[Person] ), Filter(BADetail, BADetail[SchoolPeriod] ="AM" )) I can then get the following chart to show Person# Date Period Abs Type #absent 1 9/1/2019 am sick 1.0 1 9/2/2019 am sick 1.0 1 9/3/2019 am personal 1.0 1 9/4/2019 am sick 1.0 1 9/5/2019 am sick 1.0 1 9/8/2019 am sick 1.0 ___________________________________________________________________ Total 1.0 So I created a new measure #total for Date = SUMX(BADetail, BADetail[# absent]) And this will show a correct count of 6.0 for just the 'am' absences, but not for the full days (am and pm) I have tried to alter my first measure #absent = CALCULATE ( DISTINCTCOUNT ( BADetail[Person] ), Filter(BADetail, BADetail[SchoolPeriod] ="AM" && BADetail[SchoolPeriod] ="PM" )) And there is an error code. I had thought about alternative ways of doing this, I can't figure out why this does not work? Through the process I'd like to be able to create the summary chart below The correct answer for full absences is 3.0 Person# Date #absent 1 9/1/2019 1.0 1 9/3/2019 1.0 1 9/8/2019 1.0 Eventually, I'd like to find a way to note that some people were off full day, but used two different reasons. This will be another day. Much appreciated for any thoughts? Thanks againSolved2.3KViews0likes5Comments