Forum Discussion
DistinctCount with filters
Hi,
I'm trying to create a measure using distinctcount to calcualate the number of visits made by each user in each month.
Here's what my table looks like:
Table1
| VisitID | UserID | VisitDate |
| 1 | A | 1/1/2020 |
| 2 | A | 1/1/2020 |
| 3 | B | 3/1/2020 |
| 4 | A | 1/2/2020 |
| 5 | B | 1/2/2020 |
| 6 | B | 3/2/2020 |
| 7 | A | 1/3/2020 |
| 8 | C | 1/3/2020 |
The output should look like
| UserID | Month | Visits |
| A | January 2020 | 2 |
| B | January 2020 | 1 |
| A | February 2020 | 1 |
| B | February 2020 | 2 |
| A | March 2020 | 1 |
| C | March 2020 | 1 |
Thanks in advance!
3 Replies
The above pbix has a simple measure and it works fine!
It was an issue with my relationships which I was able to resolve.
for anyone who's new to powerBI and faces this issue, remember that your active relationships work as filters for your data. Having a well modeled data model saves you a world of pain.
thanks again negi007 !
Hi,
Thanks for your reply. It's crazy because I've been racking my head trying something overly complicated. I tried using the count, countrows, distinctcount functions but I keep seeing the same value (total number of rows in the table) being repeated for each user and each month-year. I only have data starting from Nov 2020 but this function shows values from 2014 to 2099 (Entire date range of my date table). It seems like the relationships I have is where the issue is.
