Forum Discussion
Changing slicer source based on drillthrough filter
I have a report where I am comparing usage between different clients.
Each client has a different billing cycle. I have a date calendar where I have specified the billing cycles so that I can view the usage within each clients respective billing cycle rather than the calendar month or month to date.
Client1 Billing Month = IF('Date'[Day]<27, FORMAT(DATE([year],[month],1), "YYYY-MM"), FORMAT(DATE([year],[month]+1,1), "YYYY-MM"))Client2 Billing Month = IF('Date'[Day]<2, FORMAT(DATE([year],[month]-1,1), "YYYY-MM"), FORMAT(DATE([year],[month],1), "YYYY-MM"))
..etc
The main page of the report is an overview and there is a second page that you can drillthrough to based on the selected client.
My issue is that I need to be able to have a slicer with the billing month on the drillthrough page, but the ranges are different for each client.
I essentially need to be able to filter on the correct billing month once I have drilled through to a specific client. So if the filter is client=1, then it would also filter based on the Client1 billing month.
Ideally I would have a "Billing month" slicer that dynamically changes based on what client is selected.
I tried using a measure with IF(SELECTEDVALUE(Client) = 1..etc which works but I realised I cant use a measure for a slicer.
Also tried to use a calculated column to generate the correct billing range based on SELECTEDVALUE but I realised that it does not dynamically change when you change the filter.
Is there any way to acheive this or am I going about it the wrong way?
Thanks
1 Reply
- lbendlinSuper User
Read about Field Parameters.
There's also hidden synced slicers but I wouldn't go there voluntarily.