Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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) |
Solved! Go to Solution.
I solved it.
Hello @Anonymous ,
You can try the following formula:
COUNTIFS = CALCULATE ( COUNTROWS ( Table ), FILTER ( Table, Table[A] = [A] && Table[B] = [B]
&& Table[C] = [C] ) )
I solved it.
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 |
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 |
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
61 | |
61 | |
55 | |
38 | |
27 |
User | Count |
---|---|
82 | |
61 | |
45 | |
41 | |
39 |