Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |