Forum Discussion

drdataguy's avatar
drdataguy
Frequent Visitor
3 years ago

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

  • Read about Field Parameters.

     

    There's also hidden synced slicers but I wouldn't go there voluntarily.