Need DAX Level of Detail Calc.... Count Distinct

[tbucki1]

I have a simple four column table that has customer ID number, product name, date, and revenue value. In this table are a list of all different customers, the various products that they purchased on the given date and the revenue amount for each. What I want to do is create a dax formula I can use in a pivot table that shows me the unique number of customers for a given time that had generated any amount of revenue. If they are a revenue generating customer, then they are counted. For example, I might have in the year 2013 Customer-1025 who purchased three different products (A,B, C) for three different values. When I count the number of customers all for 2013 I want to see a distinct count of one next to Customer-1025 and at the bottom of my pivot table a total unique value for all customers so it should be the sum of the distinct counts. I do not want to see a count of three next to Customer 1025 indicating three different products purchased in the year 2013.

I currently have a calculated column in my data model with the following formula and have created a dax formula off of it but the resulting table is not giving me the results I am looking for. When I expand the pivot to bring in the product name underneath the customer number I should only see a count of one for each of the three products that customer 1025 purchased but the subtotal should still remain one for that customer since that customer is a distinct count for that time period.

Calculated Column       Count Check: =IF(ISBLANK('Revenue'[Value]),BLANK(),"X")

DAX Measure                 Count of Rev Customer:=COUNT([Count Check])

The calculate column is essentially checking to see that the revenue value is blank and if it's not blank put an X in that column. Then I do a count on the number of X is but all that is doing is giving me a total count of customers by product by year and I need a distinct count of customer at whatever level of detail I am looking at in my pivot. What am I doing wrong?

Below is the incorrect Pivot I built.... Showing Count of Customer purchases... Not distinct count of customers....

I need the pivot to look like this....Distinctly counting Customers regardless of how many purchases they made... Total customers is 12 in 2013.... NOT 19 like shown above... How do I correct this?

[tbucki1]

Yep that worked

[Anonymous · Accepted solution]

Hello

Not sure to undersatnd you at 100% but try this code :

Count of Rev Customer:=calculate(COUNT([Count Check]),[Count Check]="X")