Forum Discussion
Counting Only Duplicate Values That Appear Within Date Range
Hello,
I have a Table that shows Sales done to Customers. One customer can purchase two product in a span of time.
The Products are also sold at different Retailers.
| Order ID | Customer ID | Company | Date |
| 0001 | C002 | BT2 | 01.01.2019 |
0002 | C002 | BT2 | 01.01.2020 |
| 0003 | C002 | BT2 | 01.02.2020 |
| 0004 | C001 | BT1 | 01.03.2020 |
| 0005 | C003 | BT1 | 01.04.2020 |
| 0006 | C003 | BT1 | 01.05.2020 |
I want to count Customers that appear duplicated (Made more than 1 purchase) in a selected Date Range. For example 01.01.2020+
I also want to be able to group them by Companies Also.
So i would like to see
BT1 - 2 Customers (because of C003)
BT2 - 2 Customers (because of C002)
Please Help me.
Tkanchaveli , Try a measure like this
countx(filter(Summarize(Table, Table[Company], Table[Customer ID], "_1", count(Table[Order ID])),[_1] >1),[_1])
4 Replies
Tkanchaveli , Try a measure like this
countx(filter(Summarize(Table, Table[Company], Table[Customer ID], "_1", count(Table[Order ID])),[_1] >1),[_1])
sadly it didn't work. i want to count the number of times each duplicate appears also. i think your formula only counts how many duplicates there are.
I like this part of the function as it counts what i want in a table:
filter(Summarize(Table, Table[Company], Table[Customer ID], "_1", count(Table[Order ID])),[_1] >1),[_1])
but i think adding countx isn't working how i want it to work. i want to take the values the above function is giving me, but i want them to be filtered in date range and used in a vizualization
If i were to use sum in a vizualisation using
filter(Summarize(Table, Table[Company], Table[Customer ID], "_1", count(Table[Order ID])),[_1] >1),[_1]) filter, then i'd get the report i need
I added sumx and i think it worked, thanks.
