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.
I wan to know the occurance of each number as a transaction. I.e 1st transaction, 2nd transaction etc based on the count of its duplicate values. how to convert this formula in XL to power bi Countif($A$2:A2,A2) ? Need the same result
It shows the number of duplicated values of each number in the coloumn. if you see 3342 it has repeated the 2nd time next this means its the 2nd time it has come in if 3 then 3rd time etc.
With Regards
Agragesh
Sample Data:
Solved! Go to Solution.
Hi @agragesh123,
Sorry for the misunderstanding previously.
For your requirement, you can add a Index Column in Query Editor From 1, then create a calculated column use DAX like below:
Column = CALCULATE( COUNTROWS( Table2 ), FILTER( Table2, EARLIER( Table2[new or old] ) = Table2[new or old] && Table2[Index] <= EARLIER( Table2[Index] ) ))
Best Regards,
Qiuyun Yu
Hi @agragesh123,
It seems that you want to return the frequency for each “new or old” column value, right?
In your scenario, you can place “new or old” column and the third column in a table visual, and set the aggregate function for the third column as count.
Also you can model the data in Query Editor use Group By feature directly then place in a table visual.
Best Regards,
Qiuyun Yu
hi! @v-qiuyu-msft
The solution you have shown would give me a total count of each number but but the individual occurance. If you see the data refelcts each transaction by a number. hypothetically ,suppose there is a numeber 9842124555 if he has transacted for 1st time the count in the adjacent column would show 1 and in another column it would show 9842124555. Now again if he transacts it the 9842124555 would still be displayed in a new row but the count would be 2.
This is finding each duplicates in a column which enables to track each transaction by a user. what is his/her 1st transaction, 2nd transaction etc.
I am able to do so in XL using Countif but not able to figure out how to do it in Power BI
Thanks hope you can help me out.
With Regards
Agragesh
Hi @agragesh123,
Sorry for the misunderstanding previously.
For your requirement, you can add a Index Column in Query Editor From 1, then create a calculated column use DAX like below:
Column = CALCULATE( COUNTROWS( Table2 ), FILTER( Table2, EARLIER( Table2[new or old] ) = Table2[new or old] && Table2[Index] <= EARLIER( Table2[Index] ) ))
Best Regards,
Qiuyun Yu
I followed the calculate function you posted here, and my program crashed. Why would my program crash? I am working with 54k rows.
@v-qiuyu-msft Hi!
Thanks a lot ! It works thanks. I think power bi can include something like countif and more function in XL to make it easy to work with.
With Regards
Agragesh
Hi @agragesh123,
If you have any great idea, you can post it in here. Your feedback will make Power BI product become better and better.
Best Regards,
Qiuyun Yu
You can use GROUPBY or COUNT with CALCULATE to do this. Can you please post the sample data if possible.
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |