cancel
Showing results for 
Search instead for 
Did you mean: 

Cross highlighting on Date Measure produces unusual results

Good day I have a measure ([# of Exits]: The number of Employees who have left the company in a sepcific period), defined as follows:

 

 

# of Exits = 
VAR Exits =
    CALCULATE (
        COUNTROWS ( Employee ),
        FILTER (
            Employee,
            Employee[EndDate] >= MIN ( 'Date'[Date] )
                && Employee[EndDate] <= MAX ( 'Date'[Date] )
        )
    )
RETURN 
    IF (ISBLANK(Exits),0,Exits)

The measure appears to work as expected, as per the filter context.

 

image.png

However, if I filter by the Month Name on the table, the bar chart no longer calculates as expected. 

image.png

Is this the inteded behaviour? 

Status: Needs Info
Comments
Advocate II

How does your model look like? It seems you are calculating the measure on top of the employee dimension and you filter on the date dimension. How are those two related?

Regular Visitor

Hi Koen

 

Yes, the filter is on the Date dimensions, and it references a column in the Employee Dimension (Termination Date). 

 

There is no direct relationship between the two tables, the relationship is "created" by the filter within the measure.

 

My initial post may not have been that clear. When I cross filter the bar chart (by clicking on the Month Name in the table), I would expect to see that all bars retain the same values, but the selected month is highlighted.

Advocate II

The problem is twofold:

  • there's no relationship between employee and date. Since both are dimensions, this is understandable.  This means however there's no filter propagation between the 2 tables, which you solved by using FILTER and the MIN/MAX date.
  • the employees that don't have an end date (aka those who are still employed) have NULL for an end date. As you know, every query language hates NULLs 😉

What happens when you cross-filter is that for every month that is not selected, the visual tries to highlight the data for those months + the selected month (this is how highlighting works. It's a bit weird now because you're highlighting the same column as the one on the axis).

For example, you click on April 2018. For the March 2018 in the bar chart, the filter becomes March 2018 (context filter) + April 2018 (highlight filter). The combination of those means any employee with an end date will be filtered out, since the date cannot be in March and April at the same time.

EXCEPT, NULL values of course 😉 The lengthy bars that you see are all the employees that don't have an end ate.

 

How to fix this? Either create a relationship between the tables (probably not an option), use a fact table to track employee retention (or another attribute of the employee dimensions. I did this in my model) or specify a dummy date for the employees without an end date (for example 2999-12-31).

 

 

Microsoft

Thank you for the comment, @koenverbeeck.

 

Hi @TheJimmyRSA, please share the pbix file. We can find the cause easier. You can delete the confidential parts first.

 

Best Regards,

Dale

Impactful Individual
Status changed to: Needs Info
 
Regular Visitor

Hi All

 

Thanks for the responses. Koen is 100% correct. I changed the measure to the following:

 

# of Exits = 
VAR Exits =
    CALCULATE (
        COUNTROWS ( Employee ),
        FILTER (
            Employee,
            Employee[EndDate] >= MIN ( 'Date'[Date] )
                && Employee[EndDate] <= MAX ( 'Date'[Date] )
                && NOT(ISBLANK(Employee[EndDate]))
        )
    )
RETURN 
    IF (ISBLANK(Exits),0,Exits)

The Measure now behaves exactly as expected on visuals.

 

Thanks @koenverbeeck