Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

filter where date = today

I have table of data.

i am using chiclet slicer for the button look

i want to be able to select a button to filter the table for entries with todays date

i created a new column and applied this

CurrentDate = IF('QR Scanning'[DateTime].[Date] = today(), "Today", "All")
this provides 2 buttons in the slicer, Today and All
Clicking Today will filter for the entries with todays date, but clicking All will show all entries other than todays date
how do i get either 
1 button - when clicked, show todays entries, when deselected shows everything
2 buttons - today shows today entries, all shows all entries
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

    I think calculated column is not a good way to achieve your goal. You can try to build a filter measure.

    My Sample:

    Firstly, let's build a new table to create a slicer.

    Table = {"All","Today"}

    Then create a measure.

    Filter =
VAR _CurrentDateTime =
    MAX ( 'QR Scanning'[DateTime] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Table'[Slicer] ),
        "All", 1,
        "Today",
            IF (
                DATE ( YEAR ( _CurrentDateTime ), MONTH ( _CurrentDateTime ), DAY ( _CurrentDateTime ) )
                    = TODAY (),
                1,
                0
            ),
        0
    )

    Build your visual and add this measure into filter field. Set this measure to show items when value equal to 1.

    Select ALL

    Select Today.

     

    Best Regards,
    Rico Zhou

     

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

     

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous Create bookmarks for Today versus All and tie the buttons to those bookmarks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      the buttons are ok as they are, my issue is the data the All returns

      as it is tied to the query - 

      CurrentDate = IF('QR Scanning'[DateTime].[Date] = today(), "Today", "All")
      all shows everything other than today - i want it to show all
      so can i get a better formula? or filter differently?
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous 

        I think calculated column is not a good way to achieve your goal. You can try to build a filter measure.

        My Sample:

        Firstly, let's build a new table to create a slicer.

        Table = {"All","Today"}

        Then create a measure.

        Filter =
VAR _CurrentDateTime =
    MAX ( 'QR Scanning'[DateTime] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Table'[Slicer] ),
        "All", 1,
        "Today",
            IF (
                DATE ( YEAR ( _CurrentDateTime ), MONTH ( _CurrentDateTime ), DAY ( _CurrentDateTime ) )
                    = TODAY (),
                1,
                0
            ),
        0
    )

        Build your visual and add this measure into filter field. Set this measure to show items when value equal to 1.

        Select ALL

        Select Today.

         

        Best Regards,
        Rico Zhou

         

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

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      i don't see how that helps my formula 

      i need to have 2 options with the filter

      1. see todays data

      2 see all data