Interesting Problem with Attendance

[Greg_Deckler]

Had an interesting attendance issue come up at my user group recently. Basically it centers around attendance. The goal is to present a table/matrix visualization that displays all Employees within a certain date range that did not attend Training.

The core tables are these:

Employees

Employee

Greg

Bill

Joe

David

Jimbo

Training

Employee Training Date

Greg	Training 1	Tuesday, September 19, 2017
Bill	Training 1	Tuesday, September 19, 2017
Joe	Training 1	Tuesday, September 19, 2017
David	Training 1	Tuesday, September 19, 2017
Jimbo	Training 1	Tuesday, September 19, 2017
Greg	Training 2	Saturday, August 19, 2017
Bill	Training 2	Saturday, August 19, 2017
David	Training 2	Saturday, August 19, 2017
Greg	Training 3	Wednesday, July 19, 2017
Bill	Training 3	Wednesday, July 19, 2017
Joe	Training 3	Wednesday, July 19, 2017
David	Training 3	Wednesday, July 19, 2017

Hours

Employee Week Hours

Greg	Sunday, September 17, 2017	5
Greg	Sunday, September 10, 2017	5
Greg	Sunday, September 3, 2017	5
Greg	Sunday, August 27, 2017	5
Greg	Sunday, August 20, 2017	5
Greg	Sunday, August 13, 2017	5
Greg	Tuesday, August 8, 2017	5
Greg	Tuesday, August 1, 2017	5
Joe	Sunday, September 17, 2017	5
Joe	Sunday, September 10, 2017	5
Joe	Sunday, September 3, 2017	5
Joe	Sunday, August 27, 2017	5
Joe	Sunday, August 20, 2017	5
Joe	Sunday, August 13, 2017	5
Joe	Tuesday, August 8, 2017	5
Joe	Tuesday, August 1, 2017	5

The relationships are:

Employees 1<>* Hours

Employess 1>* Training

To this, we added a disconnected table Attendance:

Attendance

Attendance

Attended

Not Attended

We then added the following measures:

NotAttended = IF(ISBLANK(CALCULATE(MAX([Date]))),"Not Attended",BLANK())

Attended = IF(ISBLANK(MAX([Date])),BLANK(),"Attended")

Measures to Show = 
IF(
     HASONEVALUE(Attendance[Attendance]),
     SWITCH(
          VALUES(Attendance[Attendance]),
          "Attended",[Attended],
          "Not Attended",[NotAttended]
	 ),
	 MAX([Date])
)

We then created a matrix visualization:

• Rows: Employees[Employee]
• Columns: Training[Training]
• Values: [Measures to Show]

And three slicers:

• List slicer for Training[Training]
• List slicer for Attendance[Attendance]
• Date range slicer for Hours[Week]

So, everything works perfectly except when dealing with putting in a range for the date range slicer as well as clicking on "Not Attended" for the Attendance slicer. What occurs is that all of the people that are filtered out by the date range slicer suddenly pop back into the matrix with all "Not Attended" values.

I sort of get maybe what is going on but I can't for the life of me figure out exactly why quite honestly or how to fix it. For whatever reason, each individual is having the attendance measures calculated for them, which returns a value even though they should be already filtered out by the matrix visualization. 

If anyone out there can recreate this and tell me what is going on and better yet how to fix it, that would be really cool. Or, if you have an alternative solution that would provide the answer, that would be great. To be specific, if you filter the date range slicer at all and choose "Not Attended", then the only person in the matrix should be "Joe", not Bill, David and Jimbo as well.

[Greg_Deckler · Accepted solution]

OK, I may have solved this, I think changing the Measures to Show measure to this fixes it:

Measures to Show = 
IF(
     HASONEVALUE(Employees[Employee]),
     SWITCH(
          VALUES(Attendance[Attendance]),
          "Attended",[Attended],
          "Not Attended",[NotAttended]
	 ),
	 MAX([Date])
)

It seems that this forces it to honor the matrix context from what I can tell.

I would still REALLY love to know what is going on with the other way though. Is it because Employee is in the matrix, then it honors the context of the matrix but since Attendance is not explicitly in the matrix, it does not honor the matrix context??