Forum Discussion

koorosh's avatar
koorosh
Icon for Post Partisan rankPost 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 starts with “Certified” and dates in 2022 year. What is the Dax ?

 

I guess it should be something like following:

 

Certified List = SUMMARIZECOLUMNS('Table1'[Col1],'Table1'[Col2],'Table1'[Col3],
FILTER('Table1',[Col1] in {"Certified" && " "}),

DATESBETWEEN(Table1[date],01 01 2020, 31 12 2020)

 

But it is not completely correct.

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"))

6 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity 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
      Icon for Post Partisan rankPost 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
        Icon for Super User rankSuper User
        4 years ago

        koorosh , Try like

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