Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Dynamically change Dates within Measure using a Slicer

Hi All 

I have a bit of an odd one which i am not entirely sure can be done but have been doing some searching and cant seem to find the right solution to my problem. 

 

I have individual visuals which are linked to a synced slicer, this is because we wanted to select in one place the year we are looking at.

 

What i am trying to do is apply the selection from this synced slicer directly to a measure we have to replicate the conditions around a visual synced to the slicer. 

 

For example, 

I have a slicer for selecting a date range, 

i have linked this directly to the page and visual i want to affect. 

we show these in their own tables on one page, and have a synced slicer linked to each table. The one thing to note is each table may use a different filter to one another. 

 

What we then do is create a summary table to show the results with all of the conditions within one table. 

 

So we have had to replicate the individual filters on each visual by creating a measure to replicate the same results, for example, 

 

Q11 First Tranche = CALCULATE([Total],
 
FILTER(DimSDRProvisionCategory,DimSDRProvisionCategory[SDRProvisionCategoryName] = "Partially Staircased"),
Filter (DimStockHierarchy, DimStockHierarchy[SDRRegisteredProviderOrganisationId] = 1 && DimStockHierarchy[LetType] = "NewLets" && DimStockHierarchy[IsHabitable] = 1),
FILTER(FactSDRUnitSnapshot,FactSDRUnitSnapshot[SDRReturnYearKey] = "2021"),
FILTER(DimStatus,DimStatus[StatusName] in {"Let","Void"}),
FILTER(DimTenancyAgreement, DimTenancyAgreement[FirstTrancheDate] <= DATE(2021,3,31) && DimTenancyAgreement[FirstTrancheDate] >= DATE(2020,4,1)))
 
What i am asking is how i can link the Date filter within this measure to the selection of the synced slicer to avoid having to go in each measure and update this every time we want this to match the individual visuals which are linked to the synced slicer. 
 
From looking about there is a way of using selectedvalue as a function but i cant see how this would work on an already implemented slicer when you need to create a table for it.
 
Any help would be very much appreciated. 
Many Thanks
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , If you plan to filter on initialize Range based slicer based on another slicer that will not work.

     

    The usual trick to filter a slicer is that we use a measure from common fact and check for not blank at visual filter level, but this does not work for date range

    example: https://www.youtube.com/watch?v=cyOquvfhzNM

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for you swift response. Yes i thought as much. I am trying to find a way to select a reporting year based on data which has a series of dates within. 

       

      I could create a new column to determine the reporting year, but its the part of linking it within a measure which updates based on the selection made from a slicer which i think will still be the issue. 

       

      Any ideas?