Forum Discussion

Vamshi2020's avatar
Vamshi2020
Icon for Helper II rankHelper II
5 years ago
Solved

Slicer Dynamism based on Dim Date Slicer

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

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Vamshi2020
      Icon for Helper II rankHelper II
      5 years ago

      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)

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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/EScQwbIyUEZMjtxReisFzzIB1lrmfaWT6jWyJILd1eq5Og?e=LQbL7y

     

    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