Forum Discussion
dax two dates one id
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]
))
Thanks for your reply
IT works with another formula using early
CouncilCount = CALCULATE(DISTINCTCOUNT('Sheet1'[id]), filter(Sheet1, Sheet1[pCancelDate]=earlier(CountBook[Date]) ) )
3 Replies
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.
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
