cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
charleshale Member
Member

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

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

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

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 Member
Member

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors