Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shelley
Continued Contributor
Continued Contributor

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!
1 ACCEPTED SOLUTION
Shelley
Continued Contributor
Continued Contributor

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



View solution in original post

2 REPLIES 2
PaulDBrown
Community Champion
Community Champion

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




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Shelley
Continued Contributor
Continued Contributor

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



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.