cancel
Showing results for
Did you mean:
Regular Visitor

## How to get count of one column based on refrence to other column

Hi Guys,

I am trying to get the count of repeat appointments to a particular Task. But I am not able to get the same.

See the below table, The task that I have striked through has 2 different appointments but it is being counted as 1. It should be 2. Like same task - 2 appointments.

Is there are way I can get this calculation done in POWERBI ?

Any help on this would be a lot helpful for me to calculate the no appointments taken for each task!

Please note I dont want to group the data by copying the Table & grouping it separately .

I need to either have a column or a measure created for the same as there are also some tasks where the appointment is Blank. So I need some calculation like

IF(Appointment=blank, '0' else count Task where Appointment greater than 1)

 Task Appointment Count 2274257 190992 1 2274262 190993 1 2274266 190995 1 2274267 190996 1 2284187 191167 1 2284192 191168 1 2284192 191169 1 2284212 191175 1 2284231 0 2284238 191185 1 2284251 191192 1 2284617 191197 1

Many Thanks,

Prathamesh Sable

4 ACCEPTED SOLUTIONS

Accepted Solutions
Microsoft

## Re: How to get count of one column based on refrence to other column

Hi @prathameshsable,

Please create a calculated column using the formula below.

`count=IF(Table[Appointment]=blank(),0,CALCULATE(COUNT(Table[Task]),ALLEXCELT(Table,Table[Task])))`

If data type of [Task] is Text, please use COUNTA rather than COUNT.

Best Regards,
Angelia

Regular Visitor

Thanks Angelia

Microsoft

## Re: How to get count of one column based on refrence to other column

Hi @prathameshsable,

Best Regards,
Angelia

Regular Visitor

## Re: How to get count of one column based on refrence to other column

Thanks Angelia....

5 REPLIES 5
Microsoft

## Re: How to get count of one column based on refrence to other column

Hi @prathameshsable,

Please create a calculated column using the formula below.

`count=IF(Table[Appointment]=blank(),0,CALCULATE(COUNT(Table[Task]),ALLEXCELT(Table,Table[Task])))`

If data type of [Task] is Text, please use COUNTA rather than COUNT.

Best Regards,
Angelia

Regular Visitor

Thanks Angelia

Microsoft

## Re: How to get count of one column based on refrence to other column

Hi @prathameshsable,

Best Regards,
Angelia

Regular Visitor

## Re: How to get count of one column based on refrence to other column

Thanks Angelia....

Regular Visitor

## Re: How to get count of one column based on refrence to other column

Thank you so much for getting this resolved.

It helped a lot.

Regards,

Prathamesh Sable

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!