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
KMcCarthy9
Helper IV
Helper IV

Calculated Column Counting the Number of Repeat Instances - Not a Measure

I am in need of a calculated column that counts the number of times a vendor appears in the vendor column, 

 

I know I can do this in a measure using COUNTA, but I can't use a measure in a slicer. 

 

I also was able to achieve this by creating a table by summarizing the data, however this is a disconnected table and no way to have a date relationship. 

 

So what I really need is a calculated column that counts the number of times a vendor appears in the vendor column where it can be used as a Between Slicer. Is this possible?

 

The below slicer would control the WO Count column in the visual. 

KMcCarthy9_1-1603929956128.png

 

Thanks! 

 

1 ACCEPTED SOLUTION
BI_Jo
Resolver III
Resolver III

Hi,

 

There may be a simple way of doing this but the following would work in Power Query Editor:

 

1. Duplicate the table

2. On the duplicate table use the 'Group By' button to create a summary table by vendor

3. Use the 'Merge Queries' button to join this duplicate table back to the original table.

 

Hope that's of some help

 

Jo

 

 

 

View solution in original post

2 REPLIES 2
BI_Jo
Resolver III
Resolver III

Hi,

 

There may be a simple way of doing this but the following would work in Power Query Editor:

 

1. Duplicate the table

2. On the duplicate table use the 'Group By' button to create a summary table by vendor

3. Use the 'Merge Queries' button to join this duplicate table back to the original table.

 

Hope that's of some help

 

Jo

 

 

 

@BI_Jo Yes this worked! The only thing I changed is I did not merge queries and instead kept it as a 2 column table, Vendor and Vendor Count. This allowed me to make a 1 to many relationship with another table on Vendor. 

 

Thank you! 

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.