Forum Discussion
Slicer on a Sum
What you are looking for is the disconnected table trick. I have an upcoming blog post on this in the Community Blog (under the "more" dropdown above). Basically, create a table that is unrelated to anything with your slicer values. Here is an abbreviated version of the article:
Given the set of these core tables:
Employees
| Employee |
| Greg |
| Bill |
| Joe |
| David |
| Jimbo |
Training
| Employee | Training | Date |
| Greg | Training 1 | 9/19/2017 |
| Bill | Training 1 | 9/19/2017 |
| Joe | Training 1 | 9/19/2017 |
| David | Training 1 | 9/19/2017 |
| Jimbo | Training 1 | 9/19/2017 |
| Greg | Training 2 | 8/19/2017 |
| Bill | Training 2 | 8/19/2017 |
| David | Training 2 | 8/19/2017 |
| Greg | Training 3 | 7/19/2017 |
| Bill | Training 3 | 7/19/2017 |
| Joe | Training 3 | 7/19/2017 |
| David | Training 3 | 7/19/2017 |
Hours
| Employee | Week | Hours |
| Greg | 9/17/2017 | 40 |
| Greg | 9/10/2017 | 40 |
| Greg | 9/3/2017 | 40 |
| Greg | 8/27/2017 | 40 |
| Greg | 8/20/2017 | 40 |
| Greg | 8/13/2017 | 40 |
| Greg | 8/8/2017 | 40 |
| Greg | 8/1/2017 | 40 |
| Joe | 9/17/2017 | 24 |
| Joe | 9/10/2017 | 24 |
| Joe | 9/3/2017 | 24 |
| Joe | 8/27/2017 | 24 |
| Joe | 8/20/2017 | 24 |
| Joe | 8/13/2017 | 24 |
| Joe | 8/8/2017 | 24 |
| Joe | 8/1/2017 | 24 |
The Employees table is simply a unique list of Employee ID's. The Training table has a row for every training event that an employee attended. Finally, the Hours table lists the week ending date when an employee worked. Employees are expected to attend training events when they are working and are not expected to attend training events when they are not working. The Hours table has been abbreviated.
The relationships between the tables are as follows. All relationships are between the Employee columns of the tables.
Employees 1<>* Hours
Employees 1>* Training
Measures:
Attendance = IF(ISBLANK(MAX([Date])),"Not Attended","Attended")
Attended = IF(ISBLANK(MAX([Date])),BLANK(),"Attended") NotAttended = IF(ISBLANK(MAX([Date])),"Not Attended",BLANK())
Measures to Show =
IF(
// This first IF condition forces the measure to evaluate in the context of the visual
HASONEVALUE(Employees[Employee]),
// This next condition avoids getting the error that the visual cannot be displayed
IF(HASONEVALUE(Attendance[Attendance]),
// This switch statement uses the values from our Attendance slicer to determine which measure to display
SWITCH(
VALUES(Attendance[Attendance]),
"Attended",[Attended],
"Not Attended",[NotAttended]
),
// If the Attendance slicer has not been selected, just display the date of the training
MAX([Date])
)
)