Measure with Filter and Slicer

[FrageFrosch7]

Hi everyone,

I’m trying to write a measure that counts the number of employees in the dim_employee table for a specific team, depending on which months are selected in the MonthShort slicer (a text column what is sorted by another Monthnumber column to garantee the right order) and which team is selected in the team slicer (also a text column).

Additionally, I have another table, fact_absence, with a column named FTE, where I want to count only the employees with a value of 1.0 in that column for the month selected in the slicer. If an employee leaves the company, their value in this column becomes null.

I’ve written a measure that works when only one month is selected. However, when multiple months are selected, it should only count the employees from the most recent month selected, not from all the months. This functionality is currently not working. Can you please help me?

Employeecount per team with FTE V2 =

VAR SearchText = SELECTEDVALUE(dim_employee[Team])

VAR MaxMonth =

    MAXX(

        TOPN(

            1,

            VALUES(dim_date[Monthshort]),

            dim_date[Monthshort],

            DESC

        ),

        dim_date[Monthshort]

    )

RETURN

CALCULATE(

    COUNTROWS(dim_employee),

    dim_employee[Team] = SearchText,

    FILTER(

        dim_employee,

        COUNTROWS(

            FILTER(

                fact_absence,

                fact_absence[FTE] > 0 &&  

                fact_absence[userkey] = dim_employee[itemkey] &&

                MAX(dim_date[Monthshort]) = MaxMonth 

            )

        ) > 0

    )

)

I'd highly appreciate your help

thanks 

[rajendraongole1]

Hi @FrageFrosch7 - issue due to measure it is handling the calculation when multiple months are selected.

we can modify the approach to calculate the most recent month in the slicer and then filter only on that month

Modified one:

Employeecount per team with FTE V2 =
VAR SearchText = SELECTEDVALUE(dim_employee[Team])
-- Get the selected team
VAR MaxMonth =
MAXX(
ALLSELECTED(dim_date[Monthshort]),
dim_date[Monthshort]
)
-- Get the most recent month selected from the slicer
RETURN
CALCULATE(
COUNTROWS(dim_employee),
dim_employee[Team] = SearchText,
FILTER(
dim_employee,
COUNTROWS(
FILTER(
fact_absence,
fact_absence[FTE] = 1 && -- Only count employees with FTE of 1
fact_absence[userkey] = dim_employee[itemkey] &&
dim_date[Monthshort] = MaxMonth -- Only consider the most recent month
)
) > 0
)
)

Hope this works , please check

[FrageFrosch7]

Hi rajendraongole1, 

I get the logic but unfortunately if I apply your solution I get this error

I tested the variable in a card visual that works fine. If i select in my slicer Jan, Feb, Jul it displays Jul.

I appreciate your help!