Forum Discussion

koorosh's avatar
koorosh
Post Partisan
4 years ago
Solved

Filtering by date and Text

Hello Experts, With SUMMARIZECOLUMNS wants to create a new table. There are two columns that would be used for filtering. One text column and one date column. The filter context is all text that sta...
Need Help
  • koorosh's avatar
    koorosh
    4 years ago
    Certified List =
    SUMMARIZECOLUMNS('Table1'[Col1],'Table1'[Col2],'Table1'[Col3],
    FILTER('Table1',LEFT('Table1'[col1],9)="Certified"))
Samarth_18's avatar
Samarth_18
Community Champion
4 years ago

Hi koorosh ,

 

Pls try this:-

Certified List =
SUMMARIZECOLUMNS (
    'Table1'[Col1],
    'Table1'[Col2],
    'Table1'[Col3],
    FILTER (
        'Table1',
        [Col1]
            IN { "Certified", " " }
                && (
                    Table1[date] >= DATE ( 2020, 01, 01 )
                        && Table1[date] <= DATE ( 2020, 12, 31 )
                )
    )
)

 

Thanks,

Samarth

  • koorosh's avatar
    koorosh
    Post Partisan
    4 years ago

    Certified List =

    SUMMARIZECOLUMNS (

        'Table1'[Col1],

        'Table1'[Col2],

        'Table1'[Col3],

        FILTER (

            'Table1',

            [Col1]

                IN { "Certified", " " }))

     

    On the first try, the first part does not work.

    • amitchandak's avatar
      amitchandak
      Super User
      4 years ago

      koorosh , Try like

      SUMMARIZECOLUMNS (
      'Table1'[Col1],
      'Table1'[Col2],
      'Table1'[Col3],
      FILTER (
      'Table1',
      [Col1]
      IN { "Certified", " " } || isblank('Table1'[Col1]) ))

      • koorosh's avatar
        koorosh
        Post Partisan
        4 years ago

        No Amit. Does not work.

        Certified List =

        SUMMARIZECOLUMNS('Table1'[Col1],'Table1'[Col2],'Table1'[Col3],

        FILTER('Table1'[Col1] IN {"Certified"," "}||ISBLANK('Table1'[Col1]))