cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
skanord
Frequent Visitor

Running occurance list appended as index

Hello, i am new to DAX

I've searched all over, but i can't find a solution to my problem.

My data is as follows:

Part number
AB
AB
1234
M4
M4
M4

I need to make an index of the running occurance of the part number, and append the count to the part number, like this:

Part number     Index
ABAB1
ABAB2
123412341
M4M41
M4M42
M4M43

The table is called 'Search file' and it has 10 columns. 

Thank you in advance to anyone who can help me out! 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @skanord 

 

To add a index column in power editor and create a calculate column below:

 

Column =
[Part number]
    & COUNTAX (
        FILTER ( 'Table', [Part number] = EARLIER ( [Part number] ) ),
        [Part number]
    )

 

Result:

v-angzheng-msft_0-1618912387393.jpeg

It is important to note that the Index field does not summarize.

 

 

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-angzheng-msft
Community Support
Community Support

Hi, @skanord 

 

To add a index column in power editor and create a calculate column below:

 

Column =
[Part number]
    & COUNTAX (
        FILTER ( 'Table', [Part number] = EARLIER ( [Part number] ) ),
        [Part number]
    )

 

Result:

v-angzheng-msft_0-1618912387393.jpeg

It is important to note that the Index field does not summarize.

 

 

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

CTozzi
Helper II
Helper II

Hi @skanord , I would create the Index in Power Query following this https://www.youtube.com/watch?v=7CqXdSEN2k4

 

In Summary:

  1. Group by Part Number
  2. Insert custom column with Index: Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([Group], "Index", 1, 1))
  3. Expand Index
  4. Make sure both columns have the same type
  5. Concatenate PartNumber & Index
  6. Remove unnecessary columns

(I can attach a sample file when I figure out how to do it 🙂 )

amitchandak
Super User IV
Super User IV

@skanord , Add an index column  https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

 

and create a column in dax

[Part number] & countx(filter(table, [index] <=earlier([index]) && [Part number] = earlier([Part number])),[Part number])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors