Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Filter a table with a slicer

Hi All,

 

I'm having some troubles with a filtering in a report. Basically there is :

- date dimension

- a work calendar

-an employee dimension

- a termination dimension

- an entry dimension

 

 

what i would need that based on the date slicers on the pages, the TERMINATION should filter the EMPLOYEE dim such as for instance:

 

 someone has a termination date in 2020/10/01 , that person should be visible if i have no date slicer selected, also if the selected date is before the termination, and also if i selected only the year 2020. But it should be not visible if i precisly select any date after 2020/10/01 

 

i know that i should do it with a Selectedvalue but somehow doesnt want to work 😕

 

Aaaah and what i need is to put this on a REPORT level (hopefully not adding it to each and every visual 😄 )

 

thanks in advance for the support

Need Help
Tips and Tricks

4 Replies

  • PC2790's avatar
    PC2790
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hello Anonymous ,

     

    As per the understanding of your requirement, I would suggest creating a relationship between Termination table and Calendar as I guess the slicer would have the date taken from the Calendar table.

    After creating the relationship,  try to make your selection using your slicer & see if it works

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello PC2790 ,

       

      the issue with that is, that on the termination table i have 400 rows, if someone has a termination date , then it's filled if it is not terminated there a 2555-01-01 future date. If i add the connection between the 2 tables, it would filter precisely the date not the "on or after". furthermore due to the current buil of the data modell, the connection between the 2 tables is possible only as a non-active relationship. 

       

      I ohpe my explanation wasn't too messy 🙂 

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

        Hi， Anonymous 

        When your slicer selects a date point, you can use SELECTEDVALUE('Calendar'[Date])  as the filter condition. However, when you select year2020, this is to filter the data to a date period (2020, 01, 01)-(2020, 12, 31),  you need to use min('Calendar'[Date]) and max('Calendar'[Date]) to get the calendar date.If you use SELECTEDVALUE('Calendar'[Date]) ,you may get a blank value.

         

        Try measure as below, then drag this measure to visual filter pane of table visual to filter the data

         

        Measure1 =
VAR maxdate =
    MAX ( 'Calendar'[Date] )
VAR termdate =
    SELECTEDVALUE ( 'Termination'[Date] )
RETURN
    IF ( ISFILTERED ( 'Calendar'[Date] ), IF ( maxdate > terndate, 0, 1 ), 1 )

         

         

        If I misunderstand what you mean, please share your pbix file to onedrive for testing.

         

        Best Regards,
        Community Support Team _ Eason