Count text values in column +1 DAX formula

[KamphuisB]

Dear Power BI Users,

I am a bit new to the DAX formulas. I have searched a lot on the forum but i haven't found my answer for the following question. 

I would like to create a calculated column to count the text duplicates in a specific order, that it looks to the previous rows of data of the column. See data example below:

ColumnA  ColumnB

A	1
B	1
C	1
D	1
A	2
E	1
F	1
B	2
A	3
D	2
G	1
H	1
A	4

How can i create calculated column B with DAX? 

Thanks for your help!

Kind regards,

Bas Kamphuis

[v-huizhn-msft · Accepted solution]

Hi @KamphuisB,

For your requirement, you’d better add a index column, and calculate the result based on the index column. I reproduce your scenario and get desired result as follows.

Click the table, right click->Edit Query. In the Power Query Editor, under Add column, click add index column highlighted in yellow. You will get the index from first rows shown in following screenshot.

Then create another calculated column using the formula below.

ColumnB = RANKX(FILTER(Test1,Test1[ColumnA]=EARLIER(Test1[ColumnA])),Test1[Index],,ASC)

 

If you have any other issue, please feel free to ask.

Best Regards,
Angelia

[KamphuisB]

Hi Angelia,

Thanks for answering my question. it worked with a calculated column!

I have one question: Is it also possible to make the RANKX column in the query editor? Then i get an expression error RANKX not recognised.

With kind regards,

Bas Kamphuis

[KamphuisB]

Hi Angelia,

I have the results that i wanted. The reason i asked the question above was that i needed a table with only the results of the RANKX =1 column. Since you can only really filter out the data inside the query editor, i made an New table with FILTER('Table','Table'[RANKX COLUMN] = 1)

With kind regards,

Bas Kamphuis

[v-huizhn-msft]

HIi @KamphuisB,

I am very glad you have resolved your issue. If you have any other issue, please feel free to ask in this forum.

Best Regards,
Aneglia

[Baskar]

very simple dude.

drag your column A in  and Drag Column B too.

In column B change the default summarization to count . Thats it .

or do u want any measure , we can ctreate it .

Measure = count(column B)

Thats it, 

let me know if any help