Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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
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
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
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
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
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
User | Count |
---|---|
84 | |
77 | |
72 | |
71 | |
55 |
User | Count |
---|---|
107 | |
98 | |
86 | |
79 | |
67 |