Hierarchy Filters Breaking when Matrix has Measure as a Value

[Anonymous]

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? 

[Anonymous]

Can you show your DAX formula?

[Anonymous]

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

[Anonymous · Accepted solution]

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?

[Anonymous]

Looks like changing it to output BLANK() has solved the issue. Thank you so much for your time!

[Anonymous]

I just added a dummy measure just checking if the Valid measure was flagging correctly. My output, with no filters applied, looks like:

When I apply a JobNumber filter, which I want to remove employees EXCEPT those with that job number listed, results in:

The first is exhibiting the expected behaviour, however I am not seeing any filtering except that it is removing all reference to the compliances for employees who are not assigned to the selected job.

I also tried adding a period into the section you highlighted and it is not showing a period in all of the blank employees.

[Anonymous]

I just also checked the first "" in the if statement, after the CALCULATE(COUNT(EmployeeMaster[EmployeeNumber]))=0 and it appears that is where the error is occuring.

What can I do to return absolutely nothing if the employee number is not found in the filter? Or prevent calculation unless the slicer matches their employee number?

[Anonymous]

Please don't judge it too harshly! I need to clean up some of the flags and their mechanisms as the DAX was quickly adapted from a prior implementation.