Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to Solution.
As a column:
Number of Owners = VAR __table = FILTER(ALL('Table',[Car_ID]=EARLIER([Car_ID])) RETURN COUNTROWS(__table)
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.
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)
Could you please try this..
TotalOwners = CALCULATE(COUNT(CarOwners[CarID]),FILTER(ALL(CarOwners),CarOwners[CarID] = EARLIER([CarID])))
Proud to be a PBI Community Champion
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |