Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count Unique Customers Based on a Measure

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.

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

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

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

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