Count ifs with variable range

[Anonymous]

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)

[themistoklis]

Hello @Anonymous ,

You can try the following formula:

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

[Anonymous · Accepted solution]

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]

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

[amitchandak]

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

[Anonymous]

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