Forum Discussion

Shelley's avatar
Shelley
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

How do I add another FILTER condition to SELECTCOLUMNS Statement

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/20210800424639
10/1/20210800424634
9/1/20210800424639
9/1/20210800424634
8/1/20210800424639
8/1/20210800424634
7/1/20210800424639
7/1/20210800424634
6/1/20210800424639
6/1/20210800424634
5/1/20210800424639
5/1/20210800424634
4/1/20210800424639
4/1/20210800424634
3/1/20210800424639
3/1/20210800424634
2/1/20210800424639
2/1/20210800424634
1/1/20210800424639
1/1/20210800424634
12/1/20200800424639
12/1/20200800424634
11/1/20200800424639
11/1/20200800424634
10/1/20200800424639
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!
Need Help
  • Shelley's avatar
    Shelley
    5 years ago

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



2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Shelley
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

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