Distinct count to find Attendance using multiple filters including AND

[SO]

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 again 

[az38]

Hi @SO 

try a measure

#absent = 
var _countAbsByDay = if(calculate(COUNTROWS('Table');ALLEXCEPT('Table';'Table'[Person#];'Table'[Date]);'Table'[Period]="am")>0;1;0) + if(calculate(COUNTROWS('Table');ALLEXCEPT('Table';'Table'[Person#];'Table'[Date]);'Table'[Period]="pm") >0;1;0)
return
if(_countAbsByDay=2;1;0)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

why don't you try simle distinct count measure like this

full absence =

var check=CALCULATE(DISTINCTCOUNT('New Text Document'[Period]))

return

IF(check=2,1,0)

Drag only person and date and full absense measure in table visual.

And add measure in  visual level filter  and set it to 1.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...

[SO]

Dear Parvin, 

Thank you so much for your help.  Your code works great for 90 % of my issue (aka the chart)!  The part that I still can't seem to resolve now is the total.  The table does still shows a total of (1).   

Do I need to create a new measure to sum this measure?   I tried using Full Absence Total = MAXX('Table', New Text Document[#full absence]) without success.  Thoughts? 

FYI:  Your code is similar to the code propose earlier, but simpler and with the extra instruction about setting the measure to 1.    

Again - So many thanks !

[Anonymous · Accepted solution]

So you want total at the end of table ?

if yes

then try something like this

Measure = var a=SUMMARIZE(Sheet2,Sheet2[#Person],Sheet2[Date],"total",DISTINCTCOUNT(Sheet2[Period]))

return

sumx(a,if([total]=2,1,0))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...

[SO]

Hello again Parvin, 

After sometime, the data set became so large that your first solution worked great, but started to slow down

Measure = var a=SUMMARIZE(Sheet2,Sheet2[#Person],Sheet2[Date],"total",DISTINCTCOUNT(Sheet2[Period]))

return

sumx(a,if([total]=2,1,0))

I tried your second solution 

Measure = var a=SUMMARIZE(Sheet2,Sheet2[#Person],Sheet2[Date],"total",DISTINCTCOUNT(Sheet2[Period]))

return

sumx(a,if([total]=2,1,0))

And it will will show a 1 or 0 per day, but I'm unable to calculate a total.  When I use a Matrix, the total still only shows a total of 1.    Any thoughts?  I'm really trying to isolate people with running total greater than 5,10 and 15.

Any thoughts again would be much appreciated!

Thanks again