Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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 |
User | Count |
---|---|
81 | |
75 | |
70 | |
42 | |
36 |
User | Count |
---|---|
114 | |
56 | |
52 | |
43 | |
42 |