Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Apply Two Date Filters to a Table Visual

Hi,

 

I am looking for some help where i would like to apply 2 date filters on a table visual. I would like the results in the table to be filtered if:-

 

Design Date is >= 01 Jan 2022 and <= 31 Jan 2022    OR

Date Ordered >= 01 Jan 2022 and <= 31 Jan 2022

 

At the minute the filter is filtering the visual where both dates meet the criteria above. I would like this to be filtered if either dates filters are met.

 

Thanks

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , Try a new measure. Only measure can use slicer value. Calculated column can not use the same

     

    //Date1 is independent Date table
    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _min = Minx(allselected(Date1),Date1[Date])
    Var _cnt =
    countrows(filter( 'Table', ( 'Table'[Design Date] >=_min && 'Table'[Design Date] <=_max) || ( 'Table'[Ordered Date] >=_min && 'Table'[Ordered Date] <=_max) ) )

     

    return

    if(isblank(_cnt) , "No", "Yes")

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Try a new measure. Only measure can use slicer value. Calculated column can not use the same

     

    //Date1 is independent Date table
    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _min = Minx(allselected(Date1),Date1[Date])
    Var _cnt =
    countrows(filter( 'Table', ( 'Table'[Design Date] >=_min && 'Table'[Design Date] <=_max) || ( 'Table'[Ordered Date] >=_min && 'Table'[Ordered Date] <=_max) ) )

     

    return

    if(isblank(_cnt) , "No", "Yes")

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Create an independent date table , do not join or have all joins as inactive. Use slicer from Independent date table

     

    then create measure like

    //Date1 is independent Date table
    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _min = Minx(allselected(Date1),Date1[Date])
    return
    calculate( sum(Table[Value]), filter('Table', ( 'Table'[Design Date] >=_min && 'Table'[Design  Date] <=_max) || ( 'Table'[Ordered Date] >=_min && 'Table'[Ordered Date] <=_max) ) )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for your help. On the return i dont need to SUM any values, id rather just have a return a Yes/No should the dates filters meet the criteria set. Then i can apply the measure as a visual filter where it equal 1 or Yes.

       

      Is there a way to do this rather than a sum?

       

      Thanks