cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AndyCJohnston Frequent Visitor
Frequent Visitor

Power BI - Need to count distinct users within a BIN date group

Hi, I'm kinda new to this, but have a good programming history.  I've looked around for different solutions to this, and have not come up with anything I can use.

 

Raw data is "CreateDate" (of type date), and "CreatorName" with the users AD username.

I have a table with dates.   I've used a Bin by 1 day to create a table with different count values. This one just counts all the records in the table.

bin-table.jpg

The Bin for "Create Date" is this:

Bin-Days.jpg

 

I created a simple Column for "Create Count" which is "Create Count = 1".   I have some other sum/if columns that also work ok.  

 

Stuff like this works well (and groups by the BIN):  

Created by Andy = IF(
    ISERROR(
        SEARCH("Andy Johnston", Table[CreatorName])
    ),
    1,
    0
)
 
The PROBLEM! - DISTINCT USER COUNT.
 
I need a distinct count of each user per bin (the bin in this case is the day, but I have month bins too) - so, every distinct user that's got a record on that given day?
 
I've tried a few different ways that I've found on here, but to no avail.   This is the latest one I've got.  (but I am not bound to this method - I just hoped it would work, but doesn't as you can see above)
 
Create Distinct Users = var _table = SUMMARIZE(Table,Table[CreateDate (Bin)],"distinctcount",DISTINCTCOUNT(Table[CreatorName]))
return
SUMX(_table,[distinctcount])
 
Any help would be much appreciated....maybe it's just the end of the day and I can't see the forrest for the trees!!??!!
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Power BI - Need to count distinct users within a BIN date group

Hi,

May be i am over simplifying this but drag Date/Month from the Date Table and write this measure

=DISTINCTCOUNT(Table[CreatorName])


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Power BI - Need to count distinct users within a BIN date group

Hi @AndyCJohnston 

 

Try something like this.

Measure = 
COUNTROWS(
    SUMMARIZE( 'Table', 'Table'[CreateDate ], 'Table'[CreatorName] )
)
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.
AndyCJohnston Frequent Visitor
Frequent Visitor

Re: Power BI - Need to count distinct users within a BIN date group

Thanks @Mariusz , I did try something like this also earlier yesterday.  It does make sense programatically too, but I don't understand the output AT ALL?!  Because the source table only has 32k rows in it???  So how could I be getting numbers higher than this? (I'm really confused by this, it just shouldn't be that hard surely?)

 

I tried with the Bins date, and the normal (CreateDate) dates, but neither return anything close to what I want (both well over 32k)

 

Create Distinct Users = COUNTROWS(
SUMMARIZE( BillingBOT, BillingBOT[CreateDate (Bins)], BillingBOT[CreatorName] )
)
 

 

 

bin-table.jpg

AndyCJohnston Frequent Visitor
Frequent Visitor

Re: Power BI - Need to count distinct users within a BIN date group

Any help with this?  I've been trying lots of different things, but I can't figure it out.   The numbers are all really large, so I'm figuring it must be something to do with the Binning of the date, and the overall counts of anything unique multiplied by the bins perhaps?  (otherwise I don't know how I can go from 32k rows, to a distinct count of somewhere in the 000's?)

Super User
Super User

Re: Power BI - Need to count distinct users within a BIN date group

Hi,

May be i am over simplifying this but drag Date/Month from the Date Table and write this measure

=DISTINCTCOUNT(Table[CreatorName])


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

AndyCJohnston Frequent Visitor
Frequent Visitor

Re: Power BI - Need to count distinct users within a BIN date group

Ahhh - Got it!   It's a combination of this Solution, which is correct in it's own way - but REALLY important also is that fact that it's a MEASURE - NOT a Column.

 

I did the exact same thing as a Measure, and Column.   Column gives a few hundred thousand on each row!, and the measure gives the correct count. 

 

I'll just search up what the differences are between the two, but for now, that's got me where I needed to go.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)