Forum Discussion
Hierarchy Filters Breaking when Matrix has Measure as a Value
Hello,
I am developing a dashboard to show when employees have completed a specific training item.
My problem is that, upon introducing a measure into the Values field, it will display ALL employees, regardless of filters, and simply return blank results in the fields for the employees that should have been filtered. I would like to be able to introduce a slicer on the page which would allow for selection of subsets of employees to view.
The resultant table looks like below:
| Employee Name | First Aid | License |
| John Doe | 1/1/2019 | 1/1/2019 |
| Jane Doe | EXPIRED | EXPIRED |
| Bob Dole | EXPIRED | EXPIRED |
When I apply filters by Job for job A, we SHOULD be getting (and I can get using calculated columns for each training ID individually):
| Employee Name | First Aid | License |
| John Doe | 1/1/2019 | 1/1/2019 |
However instead I'm seeing:
| Employee Name | First Aid | License |
| John Doe | 1/1/2019 | 1/1/2019 |
| Jane Doe | ||
| Bob Dole |
My data set is organized into an employee table, a training table, and a job table. There are additional ancillary tables, however they are not pertinent as this error is produced using just those tables.
| Employee Number | Employee Name | Primary Job |
| 1 | John Doe | A |
| 2 | Jane Doe | B |
| 3 | Bob Dole | C |
This table is related to the Job table by the Primary Job key, and the training table by the emloyee number.
The Job table:
| Job Number | Job Description |
| A | Hotel |
| B | Restaurant |
| C | Office |
This table is only related to the employee table in this example. There are connections to additional tables which provide additional filter capability for categories of job and similar, however they are again not relevant as the issue occurs regularly.
The training table:
| Training ID | Training Description | Employee Number | Completion Date |
| 1 | First Aid | 1 | 1/1/2019 |
| 2 | License | 1 | 1/1/2019 |
This table is related to the employee table by the employee number key.
First, I developed a measure which checks for the largest date in my completion date column in the training table. This measure is working, and returns the completion date OR text saying expired if the completion date either does not exist or is below a threshold. The measure produces the desired results in a card, and even in the final matrix.
I used the training descriptions as my columns in a matrix, with the rows being employee name from the Employee table. I then used my measure as the values field.
The error was reproduced when using just the descriptions from the Training table.
Overall, the measure is working though it is breaking my filters/hierarchy. The matrix should look something like this:
When I apply filters by Job for job A, we SHOULD be getting (and I can get using calculated columns for each training ID individually):
| Employee Name | First Aid | License |
| John Doe | 1/1/2019 | 1/1/2019 |
However instead I'm seeing:
| Employee Name | First Aid | License |
| John Doe | 1/1/2019 | 1/1/2019 |
| Jane Doe | ||
| Bob Dole |
Is there anyone with a similar issue or some insight into solutions?
I'm guessing this part of your code:
VAR ComplianceOutput = IF( CALCULATE(COUNT(EmployeeMaster[EmployeeNumber]))=0,"", IF( Valid > 0, ComplianceDate&" ", <---------------- HERE IF( Present > 0, "EXPIRED - "&ComplianceDate, "EXPIRED" ) ) )Is what's adding a blank value to your tables. If there are any instances where the employee is labeled as valid, but does not have a Due Date then it's just a blank. Can you check your data to see if there are indeed employees labeled as valid, but with no due date?
7 Replies
- ComplianceDisplay =VAR ComplianceDate =CALCULATE(MAX(Compliances[DueDate]))VAR Valid =IF(ComplianceDate > 0 ,1,0)VAR Present =IF(ComplianceDate > 0,1,0)VAR ComplianceOutput =IF(CALCULATE(COUNT(EmployeeMaster[EmployeeNumber]))=0,"",IF(Valid > 0,ComplianceDate&" ",IF(Present > 0,"EXPIRED - "&ComplianceDate,"EXPIRED")))RETURN ComplianceOutput
I'm guessing this part of your code:
VAR ComplianceOutput = IF( CALCULATE(COUNT(EmployeeMaster[EmployeeNumber]))=0,"", IF( Valid > 0, ComplianceDate&" ", <---------------- HERE IF( Present > 0, "EXPIRED - "&ComplianceDate, "EXPIRED" ) ) )Is what's adding a blank value to your tables. If there are any instances where the employee is labeled as valid, but does not have a Due Date then it's just a blank. Can you check your data to see if there are indeed employees labeled as valid, but with no due date?