How do I add another FILTER condition to SELECTCOLUMNS Statement

[Shelley]

Hi All,
I have a Service Entitlement table that has contract# and contract start and end dates in it (among other things). I want to generate another table that includes the first of the month and the contract# for every month a contract is active. I have this and it works.

Service Contract Active Dates =

DISTINCT(SELECTCOLUMNS (

    GENERATE ('Services Entitlements',

        FILTER (ALLNOBLANKROW ( 'Date Table'),

        AND ('Date Table'[Date] >= 'Services Entitlements'[Contract Start Date],

            'Date Table'[Date] <= 'Services Entitlements'[Contract End Date]

            )

        )

    ),

    "Date", STARTOFMONTH('Date Table'[Date]),

    "%0Contract Number", 'Services Entitlements'[%0ContractNumber]

))

Date%0Contract Number

10/1/2021	0800424639
10/1/2021	0800424634
9/1/2021	0800424639
9/1/2021	0800424634
8/1/2021	0800424639
8/1/2021	0800424634
7/1/2021	0800424639
7/1/2021	0800424634
6/1/2021	0800424639
6/1/2021	0800424634
5/1/2021	0800424639
5/1/2021	0800424634
4/1/2021	0800424639
4/1/2021	0800424634
3/1/2021	0800424639
3/1/2021	0800424634
2/1/2021	0800424639
2/1/2021	0800424634
1/1/2021	0800424639
1/1/2021	0800424634
12/1/2020	0800424639
12/1/2020	0800424634
11/1/2020	0800424639
11/1/2020	0800424634
10/1/2020	0800424639
10/1/2020	0800424634

However, I now want to add an additional filter so the resulting table contains only those contracts that currently have a header status of Released. So, it would be something like this FILTER('Services Entitlements'[Header Life Cycle Status] = "Released")  -- but where do I insert this in my DAX statement? Or do I have to accomplish this another way?

Help is greatly appreciated. Thanks!

[PaulDBrown]

Try:

Service Contract Active Dates =

DISTINCT(SELECTCOLUMNS (

    GENERATE ('Services Entitlements',

        FILTER (ALLNOBLANKROW ( 'Date Table'),

        AND ('Date Table'[Date] >= 'Services Entitlements'[Contract Start Date],

            'Date Table'[Date] <= 'Services Entitlements'[Contract End Date]

            ),  FILTER('Services Entitlements'[Header Life Cycle Status] = "Released")

        )

    ),

    "Date", STARTOFMONTH('Date Table'[Date]),

    "%0Contract Number", 'Services Entitlements'[%0ContractNumber]

))

[Shelley · Accepted solution]

Hi Paul, Thank you for the reply. I tried this too; however, Power BI gives me the red squiggles under the statement no matter where I add it to the DAX even ask you suggest.

I did find a way to do it from another blog, using a Variable, but I'm not sure this is the "best," most efficient way to do it. (So, I welcome suggestions). It seems to be working though...

Service Contract Active Dates =

VAR FilteredTable = FILTER('Services Entitlements', 'Services Entitlements'[Header Life Cycle Status] = "Released")

RETURN DISTINCT(SELECTCOLUMNS (

    GENERATE (FilteredTable,

        FILTER (ALLNOBLANKROW ( 'Date Table'),

        AND ('Date Table'[Date] >= 'Services Entitlements'[Contract Start Date],

            'Date Table'[Date] <= 'Services Entitlements'[Contract End Date]

            )

        )

    ),

    "Date", STARTOFMONTH('Date Table'[Date]),

    "%0Contract Number", 'Services Entitlements'[%0ContractNumber]

))