conditional based off of drop down slicer

[srpeters]

Hello,

I have a slicer that either represents someones hire date if still employed or their termination date if not.  I want the visuals to show all the employees who were hired on the year selected, even if they are no longer active. For this I need to count the amount of employees whos hire date was equal to the year selected, but this filter simply gives their hire date if they are currently employed, or their termination date if not. Because of this, for the card representing those hired on the date selected, it is counting those whos termination year is equal to the slicers year even if they were hired on a different year. Is there a way to change this DAX function to change the Hired card to show those hired on that year while also showing those terminated on the terminations card? I could just have a DAX that sees if their hire date is equal to the date selected like:

Measure = if(VALUES(Table[DateOfHire].[Year]) == Values(Table[YearsHired/FiredForSlicer]), COUNT(Table[DateOfHire].[Year]), COUNT(Table[DateOfTermination].[Year]))

If I do this however, it syas "A table with multiple values was supplied where a single value was expected". 

Thank you!

[v-nuoc-msft · Accepted solution]

Hi @srpeters 

It seems that you are trying to create a measure that counts the number of employees hired or terminated in a given year, based on a slicer.

However, your current measure is not working because you are using the VALUES function, which returns a table of distinct values, and comparing it with another table.

One possible solution is to use the SELECTEDVALUE function instead of the VALUES function.

The SELECTEDVALUE function returns a single value from a table, or a default value if there are multiple values.

This way, you can compare a single value with another single value, and avoid the error.

Here is an example of how you can modify your measure:

Measure = 
    IF(
        SELECTEDVALUE(Table[DateOfHire].[Year], 0) = SELECTEDVALUE(Table[YearsHired/FiredForSlicer], 0), 
        COUNT(Table[DateOfHire].[Year]), 
        COUNT(Table[DateOfTermination].[Year]) 
    )

This measure will check if the selected year from the slicer matches the year of hire for each employee.

If it does, it will count the number of employees hired in that year. If it does not, it will count the number of employees terminated in that year.

The default value of 0 is used to handle the case when there is no selection or multiple selections in the slicer.

VALUES function (DAX) - DAX | Microsoft Learn

SELECTEDVALUE function - DAX | Microsoft Learn

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[srpeters]

Thank you!!