Forum Discussion

clarkey1988's avatar
clarkey1988
Helper II
5 years ago
Solved

Count Row's with Multiple Filters

Hi, I wonder if anyone can help.

 

I am trying to do a count of rows if the True Driver Type = 1, and then legheaderdriver 1, Week Number and Year all equal the value currently in the row.

 

I feel like I'm close, but my current formula I've put in specific filter criteria for.

 

Count of Single Runs 1 = CALCULATE(COUNTROWS('Trip Detail'),FILTER('Trip Detail','Trip Detail'[True Driver Type]=1),FILTER('Trip Detail','Trip Detail'[LegHeaderDriver1]="MUNJO"),FILTER('Trip Detail','Trip Detail'[Week Number]=4),FILTER('Trip Detail','Trip Detail'[Year]=2021))

 

I thought it would be something like this, but it doesnt work:

 

Count of Single Runs 1 = CALCULATE(COUNTROWS('Trip Detail'),FILTER('Trip Detail','Trip Detail'[True Driver Type]=1),FILTER('Trip Detail','Trip Detail'[LegHeaderDriver1]='Trip Detail'[Driver 1 Prefix]),FILTER('Trip Detail','Trip Detail'[Week Number]='Trip Detail'[Week Number]),FILTER('Trip Detail','Trip Detail'[Year]='Trip Detail'[Year]))

Please can someone help. Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi clarkey1988 ,

     

    According to my understanding, you want to add a column for calculating the count of rows grouped by four columns(True Driver Type,LegHeaderDriver1,Week Number,Year), right?

     

    You could use the following formula:

     

    Column =
CALCULATE (
    COUNTROWS ( 'Trip Detail' ),
    ALLEXCEPT (
        'Trip Detail',
        'Trip Detail'[True Driver Type],
        'Trip Detail'[LegHeaderDriver1],
        'Trip Detail'[Week Number],
        'Trip Detail'[Year]
    )
)

     

     

    Here is the pbix file.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

7 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    If you have already applied filter/slicers on the page or the visual, then you do not need to specify those again in the measure.  So this simple measure should work

    =COUNTROWS('Trip Detail')

    If you have not applied filters/slicers on the page or the visual and would like to specify those in the measure instead, then the measure should be

    =CALCULATE(COUNTROWS('Trip Detail'),'Trip Detail'[True Driver Type]=1,'Trip Detail'[LegHeaderDriver1]="MUNJO",'Trip Detail'[Week Number]=4,'Trip Detail'[Year]=2021)

    Hope this helps.

     

     

    • clarkey1988's avatar
      clarkey1988
      Helper II
      5 years ago

      Hi Ashish - thank you for your response,

       

      I need this to be in a calculated column. The values typed in such as "Munjo", week number "4" and year "2021" I need to be the values that are actually in the current row. Without me typing them in.

       

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        Try this calculated column formula

        =CALCULATE(COUNTROWS('Trip Detail'),filter('Trip Detail','Trip Detail'[True Driver Type]=earlier('Trip Detail'[True Driver Type]),'Trip Detail'[LegHeaderDriver1]=earlier('Trip Detail'[LegHeaderDriver1]),'Trip Detail'[Week Number]=earlier('Trip Detail'[Week Number]),'Trip Detail'[Year]=earlier('Trip Detail'[Year])))

    • clarkey1988's avatar
      clarkey1988
      Helper II
      5 years ago

      Essentially, I'm trying to replicate the below count ifs

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi clarkey1988 ,

     

    According to my understanding, you want to add a column for calculating the count of rows grouped by four columns(True Driver Type,LegHeaderDriver1,Week Number,Year), right?

     

    You could use the following formula:

     

    Column =
CALCULATE (
    COUNTROWS ( 'Trip Detail' ),
    ALLEXCEPT (
        'Trip Detail',
        'Trip Detail'[True Driver Type],
        'Trip Detail'[LegHeaderDriver1],
        'Trip Detail'[Week Number],
        'Trip Detail'[Year]
    )
)

     

     

    Here is the pbix file.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • clarkey1988's avatar
      clarkey1988
      Helper II
      5 years ago

      Hi Anonymous ,

       

      I was out of the office last week. That is very close. However, I only want to count the rows if the true driver type value is the number 1.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi clarkey1988 ,


    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

     

    Best Regards,
    Eyelyn Qin