Creating measure to filter rows between two filtered values

[Matt0515]

Hello experts,

I am trying to create a measure to count the number of rows between two values of a column in my table.

While I can figure it out by using the filter pane, I cant seem to nail it down in a DAX expression.

Any help would be much appreciated!

I know the below doest work, but what I am trying to do is count the number of rows that have a value between 20 and 31 in the 1_AQLs_2019_TD'[Activity Length (week days)] column.

Example: - 

Pending Auto-Expire =

CALCULATE (

    COUNTROWS ( '1_AQLs_2019_TD' ),

    FILTER ( '1_AQLs_2019_TD', '1_AQLs_2019_TD'[Status] = "Not Started" ),

    FILTER ( '1_AQLs_2019_TD', '1_AQLs_2019_TD'[Activity Length (week days)] >= 20 )

    ||

    FILTER ( '1_AQLs_2019_TD', '1_AQLs_2019_TD'[Status] = "Not Started" ),

    FILTER ('1_AQLs_2019_TD', '1_AQLs_2019_TD'[Activity Length (week days)] <= 31))

Thanks again for any help!

[Fowmy · Accepted solution]

@Matt0515 

Try this measure, please

Pending Auto-Expire = 
CALCULATE (
    COUNTROWS ( '1_AQLs_2019_TD' ),
    '1_AQLs_2019_TD'[Status] = "Not Started",
    FILTER ( 
        ALL('1_AQLs_2019_TD'[Activity Length (week days)] ), 
        '1_AQLs_2019_TD'[Activity Length (week days)] >= 20 && '1_AQLs_2019_TD'[Activity Length (week days)] <= 31 
    )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[AlB]

Hi @Matt0515 

You need an AND rather than an OR. And you can use the simplified version for the filters in the CALCULATE arguments:

Pending Auto-Expire =
CALCULATE (
    COUNTROWS ( '1_AQLs_2019_TD' ),
    '1_AQLs_2019_TD'[Status] = "Not Started",
    '1_AQLs_2019_TD'[Activity Length (week days)] >= 20,
    '1_AQLs_2019_TD'[Activity Length (week days)] <= 31
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Matt0515]

Thank you this works for what I need!

Thanks again!