Forum Discussion
lnik
2 years agoFrequent Visitor
Distinct count by max date
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...
- 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.
Anonymous
2 years agoNot applicable
Hello friend,
I have tried the same formula you mentioned...its working....sharing the snaps
Please let me know the problem you are facing.
lnik
2 years agoFrequent Visitor
I want to use ut in matrix visualisation but there it shows 1 against item 3 and I expect it to be empty