topic dax two dates one id in Developer

dax two dates one id

mustafaelryah33 — Mon, 03 Oct 2022 10:31:10 GMT

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

Re: dax two dates one id

v-chenwuz-msft — Tue, 04 Oct 2022 05:45:27 GMT

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.

Re: dax two dates one id

mustafaelryah33 — Tue, 04 Oct 2022 05:52:43 GMT

Thanks for your reply

IT works with another formula using early

CouncilCount = CALCULATE(DISTINCTCOUNT('Sheet1'[id]), filter(Sheet1, Sheet1[pCancelDate]=earlier(CountBook[Date]) ) )

Re: dax two dates one id

v-chenwuz-msft — Tue, 04 Oct 2022 06:13:10 GMT

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