Showing results for 
Search instead for 
Did you mean: 
jericsinger Occasional Visitor
Occasional Visitor

DISTINCTCOUNT of Transactions given FILTERS

I have a column of sales transaction where each row is unique on a customer-invoice-sku basis. It looks like this:


InvoiceDate  InvoicePeriod  InvoiceNo  CustomerID  SKU  Qty  Total


05/01/2018   05/31/2018     I001       C001        RI   10   $100

05/01/2018   05/31/2018     I001       C001        SW   20   $150

05/01/2018   05/31/2018     I003       C002        RI   20   $200

05/01/2018   05/31/2018     I004       C003        SW   30   $300

04/14/2018   04/30/2018     I003       C001        RI   10   $100

04/14/2018   05/31/2018     I004       C002        SW   20   $300


I'm trying to create a visualization that counts number of unique customers by InvoicePeriod, filtered by SKU (or not in the case of totaling). The resulting visualization table should look something like this:


Count of customers by InvoicePeriod

SKU    04/30/2018  05/31/2018

RI     1           2

SW     1           2

All    2           3


The idea is that this table should update based on Slicer selections. I know this requires a measure, but everything I write ignores filters and just repeats the same value in every cell.


Apologize for being such a newbie. Any pointers would be sincerely appreciated. Thanks much.


Jessica_Seiya Established Member
Established Member

Re: DISTINCTCOUNT of Transactions given FILTERS

Not sure if I understand your requirement correcntly.


You can use the following DAX to get the unique count of customers filtered by SKU 


Count_Customer = COUNTROWS(GROUPBY(invoice,invoice[CustomerID],invoice[SKU]))

And then create a Matrix to get the following table:





dearwatson Member

Re: DISTINCTCOUNT of Transactions given FILTERS

I presume you tried this measure?


Customers = DISTINCTCOUNT(Table1[CustomerID])


I can't see why this wouldn't work and give you a correct result no matter which way you slice it.



"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte