Forum Discussion
Helper IIIDistinct count over a period
I have a table with Tags and Dates...... I would like to count the distinct number of times a tag appears over a time period i.e. 7 days. so if the tag appears multiple times in one day, it should only be counted once for that day and continue to the following day and so on.
For example I have the table below it ...... what I am looking for is to show that 130-FFXSH-2004B appears 5 times over the 7 day period....... 177-LSHH-0018 appeared 3 times over the 7 day period and 171-LSHH-0314 appears only once. At the end I want to be able to show a bar chart with the top 3 or 4 tags in the 7 day period.
| IPL Tag | Date |
| 130-FFXSH-2004B | 5/9/2022 |
| 130-FFXSH-2004B | 5/8/2022 |
| 130-FFXSH-2004B | 5/7/2022 |
| 130-FFXSH-2004B | 5/6/2022 |
| 130-FFXSH-2004B | 5/5/2022 |
| 171-LSHH-0314 | 5/8/2022 |
| 177-LSHH-0018 | 5/10/2022 |
| 177-LSHH-0018 | 5/8/2022 |
| 177-LSHH-0018 | 5/8/2022 |
| 177-LSHH-0018 | 5/8/2022 |
| 177-LSHH-0018 | 5/8/2022 |
| 177-LSHH-0018 | 5/5/2022 |
peteru9067 not sure if this is what you meant but if you just create this measure:
Appears = DISTINCTCOUNT('Table'[Date])
and put the IPL tag on the bar axis than you will get the distinct count of days for each one for every time period you will choose on an outside filter or slicer:
17 Replies
- SpartaBI
Community Champion
peteru9067 not sure if this is what you meant but if you just create this measure:
Appears = DISTINCTCOUNT('Table'[Date])
and put the IPL tag on the bar axis than you will get the distinct count of days for each one for every time period you will choose on an outside filter or slicer:
peteru9067Thanks a lot but what do you mean by outside filter
- SpartaBI
peteru9067 let's say I added a slicer with dates:
If I will change the dates there, the chart will show the appears for that period.
- amitchandak
Super User
peteru9067 , Try a measure like
countrows(summarize(Table, Table[Date], Table[Tag]) )
date should not have a timestamp, else create a new column
Date Only = datevalue([Date])
the measure
countrows(summarize(Table, Table[Date Only], Table[Tag]) )
- peteru9067
Thanks for the help......... you have provided the total count........ what I am looking for is unique count per day for x number of days so 177-LSHH-0018 will only appear a total of 3 times (for 5/8/2022, it will only count it as once)
FarhanAhmedyou can try this. Your model should have Date Dimension to get this result. whatever selection you will made through the filters of your date table you will get relative results.
Distinct Tag = CALCULATE(DISTINCTCOUNT('Tag'[Date]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-7,DAY))- peteru9067
Thanks.... I dont know if I understand your measure. what is 'Tag'[Date]
- FarhanAhmed
Tag[Date] is the date from your orignal table where IPL Tag and Dates column are present.
Date[Date] is the date column from your date dimension.
