cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
agragesh123 Frequent Visitor
Frequent Visitor

how to convert this from XL to power bi Countif($A$2:A2,A2) ?

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:

sample.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: how to convert this from XL to power bi Countif($A$2:A2,A2) ?

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] ) ))

 

w1.PNGw2.PNGw3.PNGw4.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
BhaveshPatel Super Contributor
Super Contributor

Re: how to convert this from XL to power bi Countif($A$2:A2,A2) ?

You can use GROUPBY or COUNT with CALCULATE to do this. Can you please post the sample data if possible.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Moderator v-qiuyu-msft
Moderator

Re: how to convert this from XL to power bi Countif($A$2:A2,A2) ?

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.

 

q6.PNG


Also you can model the data in Query Editor use Group By feature directly then place in a table visual.

 

q5.PNGq7.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
agragesh123 Frequent Visitor
Frequent Visitor

Re: how to convert this from XL to power bi Countif($A$2:A2,A2) ?

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 

Moderator v-qiuyu-msft
Moderator

Re: how to convert this from XL to power bi Countif($A$2:A2,A2) ?

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] ) ))

 

w1.PNGw2.PNGw3.PNGw4.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

agragesh123 Frequent Visitor
Frequent Visitor

Re: how to convert this from XL to power bi Countif($A$2:A2,A2) ?

@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 

 

Moderator v-qiuyu-msft
Moderator

Re: how to convert this from XL to power bi Countif($A$2:A2,A2) ?

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
nsrshkh1 Member
Member

Re: how to convert this from XL to power bi Countif($A$2:A2,A2) ?

Thanks it works  v-qiuyu-msft

 

Regards

Nasir Shaikh

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,278)