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

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)

 

TaskAppointmentCount
22742571909921
22742621909931
22742661909951
22742671909961
22841871911671
22841921911681
22841921911691
22842121911751
2284231 0
22842381911851
22842511911921
22846171911971

 

Many Thanks,

Prathamesh Sable

4 ACCEPTED SOLUTIONS
v-huizhn-msft
Employee
Employee

Hi @Anonymous,

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

View solution in original post

Anonymous
Not applicable

Hi @Anonymous,

Have you resolved your issue? If you have, please mark the helpful or right reply as answer, so more people will benefit from here.

Best Regards,
Angelia

View solution in original post

Anonymous
Not applicable

Thanks Angelia....

View solution in original post

5 REPLIES 5
v-huizhn-msft
Employee
Employee

Hi @Anonymous,

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

Anonymous
Not applicable

Thanks Angelia

Hi @Anonymous,

Have you resolved your issue? If you have, please mark the helpful or right reply as answer, so more people will benefit from here.

Best Regards,
Angelia

Anonymous
Not applicable

Thanks Angelia....

Anonymous
Not applicable

Thank you so much for getting this resolved.

 

It helped a lot.

 

Regards,

Prathamesh Sable

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.