cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## 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
Anonymous
Not applicable

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
Community Champion

Hello @Anonymous ,

You can try the following formula:

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

Anonymous
Not applicable

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])))
Anonymous
Not applicable

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
Super User

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

Anonymous
Not applicable

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors