Return the position of the value in a column

[Anonymous]

I want to return the position of a value in a column:

Column

A

A

A

B

B

C

C

D

E

If value is C, return 3.
If value is E, return 5.

[Ashish_Mathur · Accepted solution]

Hi,

I could solve it with Query Editor and a calculated column DAX formula.  In the Query Editor, insert an Index column starting with 1.  Write this calculated column DAX formula

Column 2 = CALCULATE(DISTINCTCOUNT(Data[Column]),FILTER(Data,Data[Index]<=EARLIER(Data[Index])))

Hope this helps.

[CNENFRNL · Accepted solution]

[Ashish_Mathur]

Hi,

So for the 2 rows of C, you want to see 3 two times and for the 3 rows of A, you want to see 1 three times.  Am i correct?  Also, do youw want a solution in the Query Editor?

[Anonymous]

Hi @Ashish_Mathur, That's exactly what I want. I would prefer DAX if it's possible. 

[Ashish_Mathur · Accepted solution]

Hi,

I could solve it with Query Editor and a calculated column DAX formula.  In the Query Editor, insert an Index column starting with 1.  Write this calculated column DAX formula

Column 2 = CALCULATE(DISTINCTCOUNT(Data[Column]),FILTER(Data,Data[Index]<=EARLIER(Data[Index])))

Hope this helps.

[Ritaf1983]

Hi @Anonymous
If I understand you correctly, you are interested in displaying the first instance of the value.
It can be applied as follows:
1 Add index column on PQ

2. create a simle min measure with dax and show it in the table / other visual you need

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly

[CNENFRNL]

Simple enough, group the table before adding index.

[Anonymous]

Hi @Ritaf1983. Thanks for your response. However, I want the second column to show 1,2,3,4,5 not  1,4,6,8,9.

[Ritaf1983]

In this case you can duplicate your table.

Remove duplacates, add index column as in my first solution, and yhan merge this table to yours.