Forum Discussion
Slicer Options Based on Another Slicer
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.
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.