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
Viraj16Agate
Regular Visitor

Duplicates, indexing

Hi All,

 

I am trying to index a particular column based on re-occurance of same values (Region), can anyone please suggest solution for below

 

I have following data sample

Region    Value

A             1000

B             500

C             2000

A             1000

D             2500

E             1500

 

I need solution as in following format

Region    Value      Regionoccurance

A             1000       1

B             500         1

C             2000       1

A             1000       2

D             2500       1

E             1500        1

 

Thanks in advance

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Viraj16Agate , refer to this video from Curbal, that can help. But in this you might need an index column first

 

https://www.youtube.com/watch?v=7CqXdSEN2k4

 

In dax

countx(filter(Table, [region] = earlier([region]) && [Index] <= earlier([Index]) ), [Index])

 

 

You need to create index in power query

 

 

View solution in original post

2 REPLIES 2
Fowmy
Super User
Super User

@Viraj16Agate 

You can first add an index column in Power Query or in the source then add the following column to your table in the model:

Column = 
COUNTROWS(
    FILTER( Table4 , Table4[Region] = EARLIER(Table4[Region]) && Table4[Value] = EARLIER(Table4[Value]) && Table4[Index]  <= EARLIER(Table4[Index] ))
)

 

Fowmy_0-1631518594533.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

amitchandak
Super User
Super User

@Viraj16Agate , refer to this video from Curbal, that can help. But in this you might need an index column first

 

https://www.youtube.com/watch?v=7CqXdSEN2k4

 

In dax

countx(filter(Table, [region] = earlier([region]) && [Index] <= earlier([Index]) ), [Index])

 

 

You need to create index in power query

 

 

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.

Top Solution Authors