Question on CountRows Distinct: can I use at the record level?

[pete234]

I would like to know if I can use some form of COUNT DISTINCT at the powerpivot table level?
The following works fine for counting the table but I need it to count the UNIQUE phone #'s by Salesperson and Day.
I am not sure if CALCULATE could somehow help with this or not.
It could also just return a flag of "1" if the number is unique for a day, so I can sum those later.

=COUNTROWS(DISTINCT(Calls[Destination#]))

I realize I could feed this to a pivot table and use CountDistinct but in this instance I need the data flat.

 

[miguel.escobar]

at the powerpivot table level you could create a calculated column with:
Phone# & Salesperson & day

and then do a distinctcount on that column

Hope this helps.

 

[AlbertoFerrari]

If you need to do a distinct count on many columns, a good approach can be that of using a measure like:

=COUNTROWS (
    SUMMARIZE (
        MyTable,
        MyTable[SalesPerson],
        MyTable[PhoneNumber],
        MyTable[Day]
    )
)

In this way, you avoid storing a calculated column with many distinct values, which might eat your memory pretty fast.

Alberto
SQLBI

 

[pete234]

Fortunately, I'll only have a few thousand record by week's end and then it will reset.
I opted to concatenate Name, Phone and Date and once I extract the table, use asap utilities to remove dupes but leave one which will roll count to pivot table.
Using SUMMARIZE did not yield a workable result as it still gave a running for the table itself.