Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
@Anonymous , can you explain the logic, Difficult to understand excel formula
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |