Forum Discussion

hashanr's avatar
hashanr
Frequent Visitor
6 years ago

Use two Drop down slicers as FROM /TO filter dates

Hello everyone,

 

I have this requiremnet where there is two dropdown slicers used to select from/to period. These drop down contains period in [Month] [Year] format and not in proper date format. See below screenshot,

 

 

 

 

 

 

 

 

 

 

My target is to filter my [Date] column in calander table based on these two From/To Drop Down slicers. Field used to populate drop down slicers is [Period] column in same calander table. Here is my calander table below, 

 

 

According to requirement, user want to see at least one year of data. ex : April 2019 (From Slicer) / March 2020 (To Slicer). And calander table is linked to other tables so if filtaration works that should filter rest of data 

 

Appreciate any support on this since i'm really stuck. Thanks in advance.

 

Hashan

Need Help

9 Replies

    • hashanr's avatar
      hashanr
      Frequent Visitor
      6 years ago

      amitchandak I wish i could use slicers like your article but user strickly wanted two dropdown slicers to pick periods. Anyway thanks for your attention and support

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        hashanr , you may have created two independent date/month tables and choose a month from those as from and 2 and create a measure like 

        Measure = 
var _min =maxx(ALLSELECTED('Date1'),'Date1'[Date])
var _max =maxx(ALLSELECTED('Date2'),'Date1'[Date])
return
CALCULATE(SUM('order'[Qty]),filter(all('Date'),'Date'[Date]>=_min && 'Date'[Date]<=_max))

         

         

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago

    hashanr  Because the period column is in the same table you want to filter, you will not be able to use two drop down slicers as is.

     

    You will first need to create two additional UNRELATED tables for

    PeriodStart = VALUES(Date[Period])

    and 

    PeriodEnd = VALUES(Date[Period])

     

    Make sure they are not related to the Date table in the model.

     

    Then use DATESBETWEEN(Date[Date], SELECTEDVALUE(PeriodStart[Period], MIN(Date[Date])), SELECTEDVALUE(PeriodEnd[Period]), MAX(Date[Date]))) as a filter in a CALCULATE expression. 

     

    You'll have to apply this measure as a visual level filter to every visual on the page, but at least it will meet client requirements. 

     

    Others may have more elegant solutions for you. 

     

    Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

     

    If you found this post helpful, please give Kudos.

    I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

    https://sites.google.com/site/allisonkennedycv

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      hashanr I am going to have to generally agree with AllisonKennedy here in terms of the general approach. But since you reached out to me personally and asked me to take a look, I will provide my thoughts. Generally if I had this kind of requirement, I would approach it with the two disconnected date tables and generally go the same route that has been suggested previously. I call these sorts of things "Complex Selectors" and they have a wide variety of forms but they all end with a measure returning 0 or 1 as to whether or not to display something. So, like:

       

      Measure =

        VAR __From = MAX('Slicer1'[Date])

        VAR __To = MAX('Slicer2'[Date])

      RETURN

        IF('Table'[Date] >= __From && 'Table'[Date] <= __To, 1, 0)

       

      You would then use this in your Filter pane to determine which rows in your 'Table' to display (the 1's). This probably looks pretty familiar.

       

      Now, your next requirement is to make sure that other related tables are also filtered. In this, amitchandak 's approach is by far the easiest and just works natively. I would first go back to the customer and show them this approach and explain that this works natively and anything else is kind of going to be a hack. I have found most customers are pretty reasonable. In the unfortunate case that you have an unreasonable customer, there are a few options.

       

      First, if you put values from other tables in the table that is filtered by our rather hacky complex measure, they should be filtered appropriately. Where you may run into trouble are some measures I suppose but those are rather hard to predict because we do not know wha those measures might be or what columns from other tables you wish to display. You may have to end up creating new measures that go like this:

       

      Measure =

        VAR __From = MAX('Slicer1'[Date])

        VAR __To = MAX('Slicer2'[Date])

        VAR __Table = FILTER('Table', 'Table'[Date] >= __From && 'Table'[Date] <= __To)

      RETURN

        <Some kind of operations over __Table, maybe ADDCOLUMNS and use measures/columns from other tables, etc. >

       

      Again, very difficult to be specific. But, instead of us guessing, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

      The most important parts are:
      1. Sample data as text, use the table tool in the editing bar
      2. Expected output from sample data
      3. Explanation in words of how to get from 1. to 2.

       

       

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi  hashanr 

    I would suggest you createa  YearMonth (numeric) column and use it as a slicer between type

     

    Otherwise, you need two separate table and create measure and use visual level filter as a workaround to get it, but this is much more complicated.

     

    Regards,

    Lin