Forum Discussion
DAX Help: Unique count by row
Suppose I have a dataset as follows:
Car_ID Owner_Name Number_of_Owners
1 Adam 3
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!
As a column:
Number of Owners = VAR __table = FILTER(ALL('Table',[Car_ID]=EARLIER([Car_ID])) RETURN COUNTROWS(__table)
4 Replies
As a column:
Number of Owners = VAR __table = FILTER(ALL('Table',[Car_ID]=EARLIER([Car_ID])) RETURN COUNTROWS(__table)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.
Could you please try this..
TotalOwners = CALCULATE(COUNT(CarOwners[CarID]),FILTER(ALL(CarOwners),CarOwners[CarID] = EARLIER([CarID])))
