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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
djaaiin93
Frequent Visitor

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

VisitIDUserIDVisitDate
1A1/1/2020
2A1/1/2020
3B3/1/2020
4A1/2/2020
5B1/2/2020
6B3/2/2020
7A1/3/2020
8C1/3/2020

 

The output should look like

UserIDMonthVisits
AJanuary 20202
BJanuary 20201
AFebruary 20201
BFebruary 20202
AMarch 20201
CMarch 2020

1

 

Thanks in advance!

1 ACCEPTED SOLUTION
negi007
Community Champion
Community Champion

@djaaiin93 I am sharing the PBIX file for your reference. 

 

 




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



Proud to be a Super User!


Follow me on linkedin

View solution in original post

3 REPLIES 3
djaaiin93
Frequent Visitor

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 !

negi007
Community Champion
Community Champion

@djaaiin93 I am sharing the PBIX file for your reference. 

 

 




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



Proud to be a Super User!


Follow me on linkedin

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. 
Screenshot 2021-06-14 at 09.52.52.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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