Slicer sections are being ignored within an if statement

[Jesskim96]

I'm new to Power BI and I'm trying to create a flag to indicate if an employee was still active during a particular year (which is chosen by a slicer selection). 

I have a date table which contains dates from 2020 to today and another table with the employees and the date they left the company. I want my users to be able to see based on the year they choose, which employees were still with the company. 

The issue I'm having is that even though I can calculate the max date of the year selected in the slicer, when I include this measure into an if statement, the max date is chosen as the highest date excluding the slicer selection so the calculation indicates all the employees have left regardless of the slicer selection.

The current calculation I'm doing is: 

Active Employee = if('Leaving Date'[Leave Date]<[Max Date],"No","Yes")

where 

Max Date = max('Date Table'[Date])​

I assume this is due to filters but I can't get it to work with any combinations I've tried so any help would be appreciated!

I've included a photo below of what the data looks like with the current calculations

[v-heq-msft · Accepted solution]

Hi @Jesskim96 ,
Based on your description, you can try transforming Active Employee into a MEASURE, which will take effect, here is my formula and results

Active Employee = 
IF(
    SELECTEDVALUE('Leaving Date'[Leave Date]) < [Max Date],
    "No",
    "Yes"
)

Best regards,
Albert He

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

[Jesskim96]

Hi Albert,

In addition to this, if I wanted to show the total number of "Yes" employees, how would I do this? If I do a count in a KPI then this doesn't take into account the row wise calculation of active employees.

Thanks!

[v-heq-msft]

Hi @Jesskim96 ,
You can create a measure

Total of Yes = 
VAR _t = 
SUMMARIZE(
    'Leaving Date',
    'Leaving Date'[Employee Number],
    'Leaving Date'[Leave Date],
    "Max Date",[Max Date],
    "Yes/No",IF([Active Employee] = "Yes",1,0)
)
RETURN
SUMX(_t,[Yes/No])

Final output

Best regards,
Albert He

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

[Jesskim96]

This is great, thank you!

[Jesskim96]

Thanks very much! That worked, I also realised from your workbook that the active employee measure needs to be in the date table rather than the leaving date table.

[Jesskim96]

Hi @rajendraongole1 
Thanks for your reply, unfortunately the if statement is still not working correctly and seems to be still taking the max date regardless of the slicer options selected. Do you have any other ideas of how to approach this?

Thanks!

[rajendraongole1]

Hi @Jesskim96 - Instead of using Max functin directly, which that may not respect slicer context, 

try the below calculation:

Max Date by Slicer =
CALCULATE(
MAX('Date Table'[Date]),
ALLSELECTED('Date Table')
)

you can modify your active employee measure by passing the max date slicer condition as below:

Active Employee =
IF(
ISBLANK('Employee Table'[Leave Date]) || 'Employee Table'[Leave Date] >= [Max Date by Slicer],
"Yes",
"No"
)

Hope it works.