The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Dear all
I have 2 dates and id column in one table I want to have calendar to calculate how many different id's appeared within each date, I used countrows and working good but it calculate all rows not the distinct one. I want to cxount only distinct count of id's
this the calculated column I used
CouncilCount =
var _CurrentRowValue = CountBook[Date]
RETURN
COUNTROWS(
Filter(Sheet1,
_CurrentRowValue=Sheet1[CancelDate]
))
Solved! Go to Solution.
Thanks for your reply
IT works with another formula using early
CouncilCount =
CALCULATE(DISTINCTCOUNT('Sheet1'[id]),
filter(Sheet1,
Sheet1[pCancelDate]=earlier(CountBook[Date])
)
)
Thanks for your reply
IT works with another formula using early
CouncilCount =
CALCULATE(DISTINCTCOUNT('Sheet1'[id]),
filter(Sheet1,
Sheet1[pCancelDate]=earlier(CountBook[Date])
)
)
Hi @mustafaelryah33 ,
I'm glad your problem was solved, please mark it as a solution so that more people can find it quickly.
Best Regards
Community Support Team _ chenwu zhu
Hi ,
Please use DISTINCTCOUNT(<column>) to count the distinct one. In your case, code like the following:Best Regards
CouncilCount =
VAR _CurrentRowValue = CountBook[Date]
RETURN CALCULATE( DISTINCTCOUNT( Sheet1[ID] ), FILTER( Sheet1, _CurrentRowValue = Sheet1[CancelDate] ) )
If it does not work, please provide some example data and expect output
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.