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

[AndyCJohnston]

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.

The Bin for "Create Date" is this:

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!!??!!

[Ashish_Mathur · Accepted solution]

Hi,

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

=DISTINCTCOUNT(Table[CreatorName])

[AndyCJohnston]

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.

[Mariusz]

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]

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

)

[AndyCJohnston]

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