Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
agragesh123
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

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

8 REPLIES 8
v-qiuyu-msft
Community Support
Community Support

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.

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

 

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.
Anonymous
Not applicable

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 

 

Anonymous
Not applicable

Thanks it works  v-qiuyu-msft

 

Regards

Nasir Shaikh

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.
BhaveshPatel
Community Champion
Community Champion

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.