dax two dates one id

[mustafaelryah33]

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]
))

 

[mustafaelryah33 · Accepted solution]

Thanks for your reply

IT works with another formula using early

CouncilCount = 

CALCULATE(DISTINCTCOUNT('Sheet1'[id]),

filter(Sheet1,

       Sheet1[pCancelDate]=earlier(CountBook[Date])

   )

)

[v-chenwuz-msft]

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

[v-chenwuz-msft]

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.