cancel
Showing results for
Did you mean:
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:

1 ACCEPTED SOLUTION

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

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.
7 REPLIES 7
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.
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.

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

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.
Frequent Visitor

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

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

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

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.
Frequent Visitor

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

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

## 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.
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

Announcements

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

#### 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

Find out where you can attend!

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