Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
themistoklis
Community Champion
Community Champion

Hello @Anonymous ,

 

You can try the following formula:

 

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

 

Anonymous
Not applicable

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
Not applicable

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
amitchandak
Super User
Super User

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.