Count Unique Customers Based on a Measure

[ajcooper35]

Hello PBI Community,

I am building a report that is showing customer attrition over a period of time for lost sales, volume, etc. In summary, "Count of Customers with a Value of 0 for a measure".

To do this, I am setting a visual filter where [_Selected Measure] is 0 or blank (to filter to customers where the current period is 0/blank, so we can see how many we lost. When I export the bottom left visual, there are about 3500 rows in excel, but when I do a customer Count with the same filters, I get about 11,200. When I remove the filters, the value does not change.

There are 2 tables involved in this calculation: Fuel_Wholesale_Sales_Fact and Customer_View. These tables are joined by a Customer_Key column.

The DAX I am using for the count is 

KPI_Customer_Count = 
CALCULATE(
    COUNTROWS(
        VALUES('Fuel_Wholesale_Sales_Fact'[Customer_Key])
    ),
    'Fuel_Wholesale_Sales_Fact'
)

I tried adding a filter context for [_Selected Measure] = 0 in the measure DAX but it gives an error (which I expected since it was using a measure for filter context).

I have also tried the below, but it only gives me a count of the total customers in the Customer table:

CALCULATE(
    COUNTA('Customer_View'[Customer_Desc])
)

Does anyone have any ideas on this? 

Thanks in advance.

[amitchandak]

@ajcooper35 , You measure can be like

COUNTROWS(
VALUES('Fuel_Wholesale_Sales_Fact'[Customer_Key])
)

or

countrows( filter(Customer_View, [_Selected Measure] = 0 ))

In case you want to check blank value

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

[ajcooper35]

These did not work, and the referenced article did not either unfortunately.

One of the other issues is when doing the count on Fuel_Wholesale_Sales_Fact is if the customer does not have a sale, then they will not be in the table so they wont be found/counted as a lost customer. 

[ajcooper35 · Accepted solution]

Figured it out! I had to modify one of the methods on the link you referenced.

The final DAX was:

Sumx(
    VALUES(Customer_View[Customer_Location_Desc]),
    if(
        OR(ISBLANK([_Selected Measure]), [_Selected Measure] = 0) 
            && not(ISBLANK([KPI_Churn_ComparisonPeriod_Measure])) 
            && [KPI_Churn_ComparisonPeriod_Measure] > 0,
        1,
        BLANK()
    )
)