Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear community,
I have a fact table with same country code in many rows, and I want to create a new calculated column that:
How can I do it? In DAX language or in Power Query? I have tried many times but I have not succeeded.
Can you help me?
Kind regards,
José Luis
Solved! Go to Solution.
you can try to create an index column in pq
then use DAX to create a column
Proud to be a Super User!
Hi @ryan_mayu ,
thanks for your answer. I tested and it works! It's amazing!. I would never have achieved it and I am extremely grateful that knowledge can be shared in this community. Thank you very much for your help and @v-kongfanf-msft and @vicky_ who have also wanted to help me with this, for me, difficult problem.
Thanks again to this community.
José Luis
you can try to create an index column in pq
then use DAX to create a column
Proud to be a Super User!
Hi @ryan_mayu ,
thanks for your answer. I tested and it works! It's amazing!. I would never have achieved it and I am extremely grateful that knowledge can be shared in this community. Thank you very much for your help and @v-kongfanf-msft and @vicky_ who have also wanted to help me with this, for me, difficult problem.
Thanks again to this community.
José Luis
you are welcome
Proud to be a Super User!
Thanks for the reply from @vicky_ , please allow me to provide another insight:
Hi @jlarques ,
Maybe create calculated column is a good and easy choose. You can try below formula:
Column =
VAR CUR_ = 'Table'[Country Code]
RETURN
COUNTROWS ( FILTER ( ALL ( 'Table' ), 'Table'[Country Code] = CUR_ ) ) - 1
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @vicky_ and @v-kongfanf-msft ,
thanks for your help. The @vicky_ solution gives me one everytime that same country code appears, not increase the number of times that appears.
The @v-kongfanf-msft solution gives me the total times that the country code appears. So, these are not exactly the solution that I'm looking for. In your screenshot,every time that appears a country counts the total times that this country appears and my idea is that gives me the number of times that appears the country in this row. For instance, first time that appears GER would be 0, second time would be 1, next time would be 2, etc., etc.
Thanks again.
Best regards,
José Luis
You can create a DAX column (or measure) with:
Count of Country Code = CALCULATE(COUNT(Table[Country Code]), ALLEXCEPT(Table, Table[Country Code]))
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |