Forum Discussion

CBReflect's avatar
CBReflect
Regular Visitor
3 years ago

Slicer Options Based on Another Slicer

Hi,

I have a tab for team member performance that has two slicers. One slicer is a vertical list date slicer with Year, Month and Week Beginning Date. The second slicer has a list of employees. I'm wanting to limit the employees that appear in the Employee slicer so that only those that were employed during the week noted in the Date slicer appear.

My thinking is that I have to be able to give each employee one of two values and then put a filter on the employee slicer visual to only show employees with the required value but I'm struggling getting to that point.

 

My User List imported data for staff has columns "Name", "Start Date" and "Termination Date". For staff still employed, "Termination Date" is blank.

I'm only relatively new to Power BI but my understanding was that I couldn't add a column to the User List imported data table because that wouldn't interact with the slicer on a particular page so I figured I had to use a Measure but it wants to give one result rather than one result per employee.

I created two Measures that are "Start of Week Selected" and "End of Week Selected" which are just the Monday and Sunday dates of the week selected in the slicer. I've got them showing in cards and they interact with the slicer correctly so I know they are correct.

 

My attempted measure is as below:

 

 

I could be way off base with that. I feel like it might need a FILTER or SELECTEDVALUE function or something but I don't understand what is needed or how to put it together so any help would be greatly appreciated.
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi CBReflect ,

     

    According to your statement, I think you are creating a measure. As far as I know, Power BI doesn't support us to use a column in measure directly, we need to use some aggregation functions like MAX()/MIN()/SUM() to get aggregation data in our measure. Or we will get error in our measure.

    Active Staff at Filter Date =
SWITCH (
    TRUE (),
    MAX ( 'Aircall User Listing'[Start Date] ) > [End of Week Selected], "No",
    MAX ( 'Aircall User Listing'[Termination Date] ) < [Start of Week Selected], "No",
    MAX ( 'Aircall User Listing'[Start Date] ) < [Start of Week Selected]
        && MAX ( 'Aircall User Listing'[Termination Date] ) = "", "Yes",
    MAX ( 'Aircall User Listing'[Start Date] ) < [Start of Week Selected]
        && MAX ( 'Aircall User Listing'[Termination Date] ) > [Start of Week Selected], "Yes",
    MAX ( 'Aircall User Listing'[Start Date] ) < [End of Week Selected]
        && MAX ( 'Aircall User Listing'[Termination Date] ) = "", "Yes",
    MAX ( 'Aircall User Listing'[Start Date] ) < [End of Week Selected]
        && MAX ( 'Aircall User Listing'[Termination Date] ) > [End of Week Selected], "Yes",
    "-"
)

     

    Best Regards,
    Rico Zhou

     

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

     

    • CBReflect's avatar
      CBReflect
      Regular Visitor
      3 years ago

      Thanks for the reply Rico.

       

      That is my concern - a measure needs to return one result and so can only work from one date in each column. The formula you suggested would only look for the max/largest Start/Termination date and apply that logic to every employee. I need it to return a result for every employee (every line) of the Employee data import table based on their own Start/Termination date value, not based on the MAX. I could add a column to the data table that could reference each Employee's details individually but is there any way to make that column interact with the Date slicer on a particular page?

       

      e.g.

      Employee A - Active: Yes - Start: 21/9/16 - Termination:  

      Employee B - Active: No - Start: 24/1/18 - Termination: 22/6/23

      Employee C - Active: Yes - Start: 14/6/23 - Termination: 

       

      With Week Beginning: 26/6/23 selected in the Date slicer, only Employees A and C would show in the Employee slicer (Employee B terminated before that week). With 10/4/23 selected in the Date slicer, the options in the Employee slicer would change to Employees A and B only (Employee B was still employed during that week while Employee C hadn't started yet).

       

      Alternatively, the page has call data for members of a call centre team - calls answered/made. Rather than showing the employees that were employed during the selected period (as above), is it possible for the Employee slicer to show only the staff who have call stats for the period selected in the Date slicer?

       

      e.g. Calls Made/Received

                                    29/5/23     5/6/23     12/6/23 

      Employee A:             10              54              0

      Employee B:             29              99              0 

      Employee C:             43               0              32

       

      In this case, with the Week Beginning 29/5/23 selected in the Date slicer, all three employees would appear in the Employee slicer. With 5/6/23 selected in the Date slicer, the options in the Employee slicer would change to Employees A and B only and when 12/6/23 is selected in the Date slicer, only Employee C would appear.

       

      Are either of these a possibility? I'm not sure my suggestion is the correct way to approach it and I don't think the suggested amendment will solve. I don't need it to be a measure if another way solves - looking for any solution.

       

      Thanks,

      Conrad.