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.
- lnik2 years agoFrequent Visitor
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?