Slicer Dynamism based on Dim Date Slicer

[Vamshi2020]

Hi Everyone ,

Hope all are well. I have a Provider Table which contain Provider id, Effective Date and Termination Date columns . I also have Date Dimension Table which doesnt have any relation with the Provider Table. 

In Reporting area , i have Dim Date Slicer(from Date Dimension table) and Provider id slicer . My requirement is when i select any date range in Dim Date slicer , i should see only the provider id's in Provider Id Slicer whose Effective and Termination Dates fall in selected Time frame in Dim Date slicer. 

Please help me to achieve this .

TIA

[v-deddai1-msft · Accepted solution]

Hi @Vamshi2020 ,

You can create a visual level filter for Provider Id Slicer:

Measure = var a = MAX(Provider[Effective Date]) var b = MAX(Provider[Termination Date]) return IF(MIN('Dim Date'[Date])<=a&&MAX('Dim Date'[Date])>=b,1,0)

And set it to 1:

For more details ,please refer to sample pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EScQwbIyUEZMjtxRei...

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

Best Regards,

Dedmon Dai

[amitchandak]

@Vamshi2020 , Create a measure and use that in visual level silcer and check for non blank and check

Measure =
var _max = maxx(allselected('Date'), 'Date'[Date])
var _min = minx(allselected('Date'), 'Date'[Date])
return
calculate(countrows(Table), filter(Table, Table[Termination Date] >=_min && Table[Termination Date] <=_max))

[Vamshi2020]

@amitchandak ,

I was not able to understand the suggested measure . Just want to mention my requirement again .

if i select any time frame in Dim Date slicer, i should lee list of Provider Id's in Provider Id Slicer only those who are eligible in selected time frame (based on Provider Effective and termination dates)