Filtering by date and Text

[koorosh]

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.

[Samarth_18]

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]

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]

@koorosh , Try like

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

[koorosh]

https://docs.google.com/spreadsheets/d/1H0JTuYkYUJeanlG2GFWOa7aW8-u7gzbp/edit?usp=sharing&ouid=11435...

https://drive.google.com/file/d/1oFzt3_K8CShXbOyk4gngl3v-dBtzXm6S/view?usp=sharing

Please check the sample.

[koorosh]

No Amit. Does not work.

Certified List =

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

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

[koorosh · Accepted solution]

Certified List =

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

FILTER('Table1',LEFT('Table1'[col1],9)="Certified"))