Count Row's with Multiple Filters

[clarkey1988]

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!

[v-eqin-msft]

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

[v-eqin-msft · Accepted solution]

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]

Hi @v-eqin-msft ,

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.

[Ashish_Mathur]

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]

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

[clarkey1988]

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]

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