cancel
Showing results for
Did you mean:
Regular Visitor

## Count ifs with variable range

Hello Community,

I'm using a Count ifs in excel like this

=COUNTIFS(\$C\$1:C18;C18;\$A\$1:A18;A18;\$B\$1:B18;B18)

Notice that I have a variable range that changes with the rows.

I need to do the same thins in Power BI M or Dax anda I'm having a hard time doing it.

Can anyone help me?

 A B C D 1 Type Account.Num Value Count.ifs 2 Booking 1382879 2.000,00 1 3 Other 1382879 2.000,00 1 4 Other 1382879 2.000,00 2 5 Other 1382879 325,33 1 6 Other 1382879 325,33 2 7 Other 1382879 325,33 3 8 Booking 1382879 51.718,64 1 9 Other 1436878 38.091,94 1 10 Other 1436878 38.091,94 2 11 Booking 1436878 2.000,00 1 12 Booking 1536899 2.000,00 1 13 Booking 1536899 2.000,00 2 14 Booking 1536899 325,33 1 15 Booking 1536899 51.718,64 1 16 Booking 1536899 2.000,00 3 17 Booking 1536899 325,33 =COUNTIFS(\$C\$1:C17;C17;\$A\$1:A17;A17;\$B\$1:B17;B17) 18 Booking 1536899 51.718,64 =COUNTIFS(\$C\$1:C18;C18;\$A\$1:A18;A18;\$B\$1:B18;B18)
1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

I solved it.

CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[A] = EARLIER('Table'[A])
&& 'Table'[B] = EARLIER('Table'[B])
&& 'Table'[C] = EARLIER('Table'[C])
&& 'Table'[Index] <= EARLIER('Table'[Index])))
5 REPLIES 5
Super User IV

@mbregunci , can you explain the logic, Difficult to understand excel formula

Proud to be a Super User!

Resident Rockstar

Hello @mbregunci ,

You can try the following formula:

```COUNTIFS =
CALCULATE (
COUNTROWS ( Table ),
FILTER (
Table,
Table[A] = [A]
&& Table[B] = [B]                && Table[C] = [C]
)
)```

Regular Visitor

My problem here is that I need to know when was the first, secont, third, .... time the value, which contemplates such conditions appears.

Fot exemple, in row 4 from the example sent earlier, column D assumes value equals to 2, because is the second time that the value 2.000,00 apears for condictions Type = Other, Account.Num = 1382879, and value = 2.000,00 .

 A B C D 1 Type Account.Num Value Count.ifs 2 Booking 1382879 2.000,00 1 3 Other 1382879 2.000,00 1 4 Other 1382879 2.000,00 2

Line 16 - It is the third time that the value 2.000,00 appears with the condicionts Type = Booking, Account Num = 1536899 and value is 2.000,00

 16 Booking 1536899 2.000,00 3
Regular Visitor

Hello @themistoklis , but my problem is not only this - counting how many time does the same condition appears, but I'm interested in increasing this counting every time it repeats.

If you can see lines 3 and 4 are equals when you are comparing Columns A to C.

The columns D is counting if the previous columns combinations already appeared earlier (rows 1, 2 and 3).

For line 3, is the first time that this condicion (Other, 1282879 and 2.000,00) appeared (so, Column D = 1).

For line 4, is the second time that this conditions appeared (so Columns D = 2)

 A B C D 1 Type Account.Num Value Count.ifs 2 Booking 1382879 2.000,00 1 3 Other 1382879 2.000,00 1 4 Other 1382879 2.000,00 2
Regular Visitor

I solved it.

CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[A] = EARLIER('Table'[A])
&& 'Table'[B] = EARLIER('Table'[B])
&& 'Table'[C] = EARLIER('Table'[C])
&& 'Table'[Index] <= EARLIER('Table'[Index])))

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors