Forum Discussion
Distinct count by max date
- Anonymous2 years ago
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.
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.
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?