Forum Discussion
How to count duplicates within date range as applied through slicers
In a list of DateTime, phone numbers, I need to find out dupes of phone numbers based on user applied filters/slicers against date range.
For exmaple, if user selected date range from 1st Jan 2021 to 31st Dec 2021, I need to count dupes of phone numbers with this date range. As and when user change date range, dupes count need to change please.
Appreciate your help. Thanks
Anonymous , You can create a date field , use that in slicer or join it with date table and use slicer from date table
DateTime = datevalue([DateTime])
You can have measures
M1= count(Table[Phone Numbers])
Duplicate = countx(filter(values(Table[Phone Numbers]) , [M1] >1 ), [Phone Numbers])
5 Replies
- amitchandak
Super User
Anonymous , You can create a date field , use that in slicer or join it with date table and use slicer from date table
DateTime = datevalue([DateTime])
You can have measures
M1= count(Table[Phone Numbers])
Duplicate = countx(filter(values(Table[Phone Numbers]) , [M1] >1 ), [Phone Numbers])
Hi Anonymous
If your data is in the below format
You can extract the date column in power query as follows(You can achieve this using DAX formula too)
Select Date Time column -> go to Add Column ribbon -> Date -> Date Only
In Power BI desktop create a measure as follows
It will return the number of times a phone number is duplicated for the selected date range.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- Ashish_Mathur
Hi,
Create a calculated column formula to extract Date from the Date/Time column
Date = int(Data[DateTime])
Create a Calendar Table and write calculated column formulas to exteact Year, Month name and Month number. Sort the Month name by the month number. To your visual, drag Year and Month name from the Calendar Table and select any one Year and Month name. Write these measures:
Phone number count = counta(Data[Phone])
Measure 1 = countrows(filter(values(Data[Phone]),[Phone number count]>1))
Hope this helps.
