topic Duplicates, indexing in DAX Commands and Tips

Duplicates, indexing

Viraj16Agate — Mon, 13 Sep 2021 05:57:52 GMT

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

Re: Duplicates, indexing

amitchandak — Mon, 13 Sep 2021 07:28:41 GMT

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

Re: Duplicates, indexing

Fowmy — Mon, 13 Sep 2021 07:36:42 GMT

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