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

Simple EARLIER function fail. What the heck am I missing?

I have a simple column of text labeled Key_AR in the table 'Bill1_AR' (tough table name to read, I know but I'm not changing it to Table1 in case that has something to do with the problem).    I am able to count the number of times Bill1_AR[Key_AR] is duplicated***.   What I am struggling to do, however, is use a simple EARLIER function to give each duplicate a sequential number.

image.png

I must be missing something basic but I'm not seeing it.   What I am doing is simply:

1. Creating an Index in Power Query to give me something to count against for EARLIER

2. Add Column: 

Column = CALCULATE(count(Bill1_AR[Key_AR]),Bill1_AR[Index]<=EARLIER(Bill1_AR[Index]))
 
I would expect this "Column" to return the following desired results instead of what it does above, which is only returning 1. 
 
Key_ARCount DupesIndexColumn
INV7695~consumption tax - other~336.8338321
INV7695~consumption tax - other~336.8338332
INV7695~consumption tax - other~336.8338343
INV7695~custom testing~4210.3838361
INV7695~custom testing~4210.3838372
INV7695~custom testing~4210.3838383
INV9374~custom testing~11250310631
INV9374~custom testing~11250310642
INV9374~custom testing~11250310653
INV7694~other~0218041
INV7694~other~0218052
INV7694~consumption tax - other~0218061
INV7694~consumption tax - other~0218072
INV5586~custom testing~5836.95625711
INV5586~custom testing~5836.95625722
INV5586~custom testing~5836.95625733
INV5586~custom testing~5836.95625744
INV5586~custom testing~5836.95625755
INV5586~custom testing~5836.95625766
INV5586~custom testing~7004.34325771
INV5586~custom testing~7004.34325782
INV5586~custom testing~7004.34325793
INV6345~custom testing~1416.47526591
INV6345~custom testing~1416.47526602
INV6345~custom testing~1416.47526613
INV6345~custom testing~1416.47526624
INV6345~custom testing~1416.47526635
INV7273~reprints~136.5427601

What am I missing?   I tried COUNTA.   

 

Vexed! 

 

Thank you for your suggestions

 

 

 

** code used:

 

Count Dupes = 
Var CheckCountARKey_AR = 'Bill1_AR'[Key_AR]
RETURN

CALCULATE(
COUNTROWS('Bill1_AR'),
all('Bill1_AR'),
'Bill1_AR'[Key_AR]= CheckCountARKey_AR
)

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Simple EARLIER function fail. What the heck am I missing?

Hello @charleshale 

You are getting locked in the row context of the table.  Give this a try.

Column 2 = 
VAR _Index = Bill1_AR[Index]
RETURN
CALCULATE(
    COUNTROWS( Bill1_AR),
        ALLEXCEPT( Bill1_AR,Bill1_AR[Key_AR] ),
        Bill1_AR[Index] < _Index
) +1

I use a VAR instead of EARLIER, I think it makes it easier to read. 

 

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Simple EARLIER function fail. What the heck am I missing?

@charleshale try this, add as a column

 

Column 2 = CALCULATE( COUNTROWS( 'Table'  ), ALLEXCEPT( 'Table', 'Table'[Key_AR] ), 'Table'[Index] <= EARLIER( 'Table'[Index] ) )





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Super User
Super User

Re: Simple EARLIER function fail. What the heck am I missing?

Hello @charleshale 

You are getting locked in the row context of the table.  Give this a try.

Column 2 = 
VAR _Index = Bill1_AR[Index]
RETURN
CALCULATE(
    COUNTROWS( Bill1_AR),
        ALLEXCEPT( Bill1_AR,Bill1_AR[Key_AR] ),
        Bill1_AR[Index] < _Index
) +1

I use a VAR instead of EARLIER, I think it makes it easier to read. 

 

View solution in original post

charleshale Regular Visitor
Regular Visitor

Re: Simple EARLIER function fail. What the heck am I missing?

@jdbuchanan71 - you have unlocked me from the row context.   Thank you.  This works great and is much more elegant.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 274 members 2,089 guests
Please welcome our newest community members: