cancel
Showing results for
Did you mean:
Highlighted
New Member

## DAX Help: Unique count by row

Suppose I have a dataset as follows:

Car_ID    Owner_Name  Number_of_Owners

1             Bob                  3

1             Carrie               3

2             Alex                 2

2             Bill                   2

3             Aron                1

What DAX expression would produce the column "Number_of_Owners" above?

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX

## Re: DAX Help: Unique count by row

As a column:

```Number of Owners =
VAR __table = FILTER(ALL('Table',[Car_ID]=EARLIER([Car_ID]))
RETURN COUNTROWS(__table)```

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

4 REPLIES 4
Highlighted
Super User IX

## Re: DAX Help: Unique count by row

As a column:

```Number of Owners =
VAR __table = FILTER(ALL('Table',[Car_ID]=EARLIER([Car_ID]))
RETURN COUNTROWS(__table)```

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Highlighted
New Member

## Re: DAX Help: Unique count by row

Greg_Deckler,

Thanks for your response!

A few questions:

1. When using the code you provided I'm getting an error which states "Too few arguments were pased to the FILTER function".  As such, I'm assuming that there should be a right-parenthesis after 'Table'.

```Number of Owners =
VAR __table = FILTER(ALL('Table',[Car_ID])=EARLIER([Car_ID]))
RETURN COUNTROWS(__table)```

2.  After adding the right-parenthesis after [Car_ID] I'm now getting an error which states "EARLIER/EARLIEST refers to a nearlier row context which does not exist".

Thank you!

*Edit:

Nevermind; it looks like the EARLIER statement doesn't produce this error when I create a column instead of a measure.

Highlighted
Super User I

## Re: DAX Help: Unique count by row

Could you please try this..

`TotalOwners = CALCULATE(COUNT(CarOwners[CarID]),FILTER(ALL(CarOwners),CarOwners[CarID] = EARLIER([CarID])))`

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Highlighted
Super User IX

## Re: DAX Help: Unique count by row

Awesome. If you want it as a measure, that would be:

```Measure 4 =
VAR __carid = MAX([Car_ID])
VAR __table = FILTER(ALL(Table5),[Car_ID]=__carid)
RETURN COUNTROWS(__table)```

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors