Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
@djaaiin93 I am sharing the PBIX file for your reference.
Proud to be a Super User!
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 !
@djaaiin93 I am sharing the PBIX file for your reference.
Proud to be a Super User!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |