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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Custom column to count number of unique occurrences of a key value for a PK column

In PowerQuery I want to add a custom column that provides total number of unique occurences of a key value for a primary key column 

 

For example,  new custom column(Count) should be created based on total number of unique occurences of ID for a certain Key
Below screen shot is what I'm trying to create

 

blokesh23_0-1644262390371.png

 

1 ACCEPTED SOLUTION
sevenhills
Super User
Super User

Try this:

 

ID Count per Key = 
CALCULATE(
	DISTINCTCOUNT('TableKeyCountMeasure'[ID]),
	Allexcept('TableKeyCountMeasure', 'TableKeyCountMeasure'[Key])
)
 

 

 

View solution in original post

3 REPLIES 3
sevenhills
Super User
Super User

Try this:

 

ID Count per Key = 
CALCULATE(
	DISTINCTCOUNT('TableKeyCountMeasure'[ID]),
	Allexcept('TableKeyCountMeasure', 'TableKeyCountMeasure'[Key])
)
 

 

 

AWESOME !!

Anonymous
Not applicable

Thanks for the help, this works as expected

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors