topic Re: Distinct count by max date in DAX Commands and Tips

Distinct count by max date

lnik — Mon, 24 Jun 2024 09:10:17 GMT

Hi everyone,

I am trying to calculate distinct values based on max date column.

But with measure

DistinctCount = calculate(DISTINCTCOUNT(Table1[Item]),Table1[Date Visit]=MAX(Table1[Date Visit]))

it count also Item 3 although he is not with max date.

Thank you!

Re: Distinct count by max date

Anonymous — Mon, 24 Jun 2024 10:09:46 GMT

Hello friend,
I have tried the same formula you mentioned...its working....sharing the snaps

Please let me know the problem you are facing.

Re: Distinct count by max date

lnik — Mon, 24 Jun 2024 14:11:30 GMT

I want to use ut in matrix visualisation but there it shows 1 against item 3 and I expect it to be empty

Re: Distinct count by max date

Anonymous — Tue, 25 Jun 2024 03:03:38 GMT

Hi,lnik

I am glad to help you.

According to your description, you want to distinct count by max date?

If I understand you correctly, then you can refer to my solution.

You can start by creating a new table.

Table 2 = VAR _distinct = SUMMARIZE ( 'Table', 'Table'[Item], 'Table'[Customer], 'Table'[Date Visit], "MaxDate", CALCULATE ( MAX ( 'Table'[Date Visit] ), FILTER ( ALL ( 'Table' ), 'Table'[Item] = EARLIER ( 'Table'[Item] ) ) ) ) RETURN FILTER ( _distinct, IF ( 'Table'[Item] = 3, BLANK (), 'Table'[Date Visit] = [MaxDate] ) )

Create a Measure for calculating the number of rows corresponding to each Item, and you'll end up with the result you want.

DistinctCount = VAR _currentItem = MAX ( 'Table 2'[Item] ) RETURN COUNTROWS ( FILTER ( ALL ( 'Table 2'[Item] ), 'Table 2'[Item] = _currentItem ) )

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Distinct count by max date

lnik — Wed, 26 Jun 2024 06:21:16 GMT

Thank you, works perfectly!

One more question.

Is it possible if i add slicer for year and month, when selecting a specific month it dynamically to calculate distinct count for the items based on the maximum date for that month?