Forum Discussion

Matt0515's avatar
Matt0515
Frequent Visitor
5 years ago
Solved

Creating measure to filter rows between two filtered values

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!
 
 
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    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

     

3 Replies

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

    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's avatar
      Matt0515
      Frequent Visitor
      5 years ago

      Thank you this works for what I need!

       

       

      Thanks again!

       

       

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    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