cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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?

 

 ABCD
1TypeAccount.NumValueCount.ifs
2Booking1382879    2.000,001
3Other1382879    2.000,001
4Other1382879    2.000,002
5Other1382879        325,331
6Other1382879        325,332
7Other1382879        325,333
8Booking1382879  51.718,641
9Other1436878  38.091,941
10Other1436878  38.091,942
11Booking1436878    2.000,001
12Booking1536899    2.000,001
13Booking1536899    2.000,002
14Booking1536899        325,331
15Booking1536899  51.718,641
16Booking1536899    2.000,003
17Booking1536899        325,33=COUNTIFS($C$1:C17;C17;$A$1:A17;A17;$B$1:B17;B17)
18Booking1536899  51.718,64=COUNTIFS($C$1:C18;C18;$A$1:A18;A18;$B$1:B18;B18)
1 ACCEPTED SOLUTION

Accepted Solutions

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])))

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Resident Rockstar
Resident Rockstar

Hello @mbregunci ,

 

You can try the following formula:

 

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

 

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 .

ABCD
1TypeAccount.NumValueCount.ifs
2Booking1382879    2.000,001
3Other1382879    2.000,001
4Other1382879    2.000,002

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

16Booking1536899    2.000,003

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) 

 

 ABCD
1TypeAccount.NumValueCount.ifs
2Booking1382879    2.000,001
3Other1382879    2.000,001
4Other1382879    2.000,002

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])))

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors