Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
peteru9067
Helper III
Helper III

Distinct 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 TagDate
130-FFXSH-2004B5/9/2022
130-FFXSH-2004B5/8/2022
130-FFXSH-2004B5/7/2022
130-FFXSH-2004B5/6/2022
130-FFXSH-2004B5/5/2022
171-LSHH-03145/8/2022
177-LSHH-00185/10/2022
177-LSHH-00185/8/2022
177-LSHH-00185/8/2022
177-LSHH-00185/8/2022
177-LSHH-00185/8/2022
177-LSHH-00185/5/2022
1 ACCEPTED SOLUTION
SpartaBI
Community Champion
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:
SpartaBI_1-1652263210240.png

 

View solution in original post

17 REPLIES 17
FarhanAhmed
Community Champion
Community Champion

you 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))






Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Thanks.... I dont know if I understand your measure. what is 'Tag'[Date]

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.







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




SpartaBI
Community Champion
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:
SpartaBI_1-1652263210240.png

 

Thanks a lot but what do you mean by outside filter

@peteru9067 let's say I added a slicer with dates:

SpartaBI_0-1652265049450.png

If I will change the dates there, the chart will show the appears for that period.

For some reason mine is showing 365

 

peteru9067_0-1652265816303.png

 

 

@peteru9067 show me a screenshot of your model and measure

peteru9067_0-1652266256737.png

 

 

@peteru9067 it looks like it's working 🙂 not like the previous photo. What is the issue?

 

How were you able to show 172-LSHH-0018 only 3 occurences....... using the same exact measures I show 6 occurences

@peteru9067 I saw now you excepted the solution. That means it was sorted out?

Yes..... all the solutions provided by you and others are correct...... the missing part which is critical was mentioned by @amitchandak  which is to ensure the date column does not have a "timestamp". so I created another column with just the date_value. Many many thanks.

@peteru9067 great 🙂

Yeah it isn't correct because 172-LSHH-0018 should only appear 3 times...... rather than 6

amitchandak
Super User
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]) )

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.