Report: customers with no purchase ever

[EVEAdmin]

Hi all,

from my MSSQL server, I exctracted a table with customers and a table with purchases.
I'd like to build a report that shows only customers with 0 purchases, customers who never bought any item.

Therefore, customers who are in the customers table but are not in the purchases table.

I can write a TSQL query to extract the list of those customers and build a 3rd table, however, I'd like to know whether I can do it, dinamycally, from the first 2 tables.

Thank you.

[Ashish_Mathur]

Hi,

Build a relationship from the Customer column in the Purchases Table to the Customer column in the Customers Table.  To your visual, drag Customers from the Customers Table.  Write these measure

Total Sales = SUM(Purchases[Amount])

Customers who did not buy = IF(ISBLANK([Total Sales]),1,BLANK())

Drag the second measure to your visual.

[EVEAdmin]

---

@Ashish_Mathur wrote:

Hi,

Build a relationship from the Customer column in the Purchases Table to the Customer column in the Customers Table.  To your visual, drag Customers from the Customers Table.  Write these measure

Total Sales = SUM(Purchases[Amount])

Customers who did not buy = IF(ISBLANK([Total Sales]),1,BLANK())

Drag the second measure to your visual.

---

@Ashish_Mathur 
the first measure worked fine, the second measure returned the BLANK syntax error

[EVEAdmin]

@TomMartens 

When I look at the error datails, it would seem that a comma , has been changed to dot .
But in the measure, I type comma. Same situation with the measure suggested by @Ashish_Mathur 

Can't see why, though.

[TomMartens]

Hey @EVEAdmin ,

please add an additional space in front of the comma and try again!

Regards,
Tom

[EVEAdmin]

@TomMartens 

that did the trick, same fix applied to the measure suggested by @Ashish_Mathur and that's fixed too.

So, is it meant to be like that? it is a bug ?

[TomMartens]

So,

personally I would call this a bug 🙂

From my understanding of some discussions circling around this topic, this is due to

• language of the os is not English US
• language of Power BI is set to a different language than the OS
• separator is not comma

Can you please provide your OS language, and the character that is used as a separator, I will use this information in some MSFT communication.  

This is one of the reasons why I always us an english (US) OS even if i'm located in Germany 🙂

Regards,
Tom

[EVEAdmin]

@TomMartens 

I am in a situation similar to yours.

Even though I am Italian, I use Windows 10 in English UK.

The laptop was purchased in Italy and it came with an Italian keyboard.

My keyboard settings are set to English for the language I most type in.

Italian is set for the keyboard layout.
The list separator, as set in the Regional settings, is comma ,

Power BI desktop is set to English.

End of the day, a comma is a comma and "should" not become a dot 

Please let me know if you need any more info, I'll be glad to contribute.

[TomMartens]

Thanks, for providing this information!

Regards,
Tom

[TomMartens · Accepted solution]

Hey,

I think this measure will flag the non buying customer:

_flagNonBuyingCustomer = 
var theCustomers = DISTINCT('Dimension Customer'[Customer Key])
var BuyingCustomer = DISTINCT('Fact Sale'[Customer Key])
var NonBuyingCustomer = EXCEPT(theCustomers,BuyingCustomer)
return
IF(HASONEVALUE('Dimension Customer'[Customer Key])
    ,IF(FIRSTNONBLANK('Dimension Customer'[Customer Key],0) in NonBuyingCustomer, 1, BLANK())
    ,BLANK()
)

What the measure does is this:

• Create a single column table with all the customers from the customers table
• Create a single column tabe with all the customers from the 'Fact Sale' table, this table corresponds to your purchase table
• Create a table that contains just the customers who never bought anything

Flag a customer, that is in the table of customers who never bought anything.

Hopefully this is what you are looking for.

Regards,

Tom

[Kevin_Gitonga]

Hi, this works when comparing all customers in the customers table however I need to filter out the customers table to consider only the customers who have bought in the last one year. How can I go around this?

[EVEAdmin]

---

@Kevin_Gitonga wrote:

Hi, this works when comparing all customers in the customers table however I need to filter out the customers table to consider only the customers who have bought in the last one year. How can I go around this?

---

Did you already try and apply a filter to the corresponding visualization?

[EVEAdmin]

@TomMartens 

I've finally had time to try that measure, however,  it returns an error.

The syntax for 'BLANK' is incorrect.

Any clue, please? I am moving my first steps with DAX.

[TomMartens]

Hey,

please post your measure, using the Insert Code feature:

Regards,
Tom

[Ashish_Mathur]

Hi @TomMartens,

Sure i will do so but any reason why that is better than just pasting the formula in the body of the message.

Thank you.

[EVEAdmin]

@TomMartens 
here you go

ZeroBuyers =
var theCustomers = DISTINCT(Customers[CustID])
var BuyingCustomer = DISTINCT(Invoices[InCustID])
var NonBuyingCustomer = EXCEPT(theCustomers,BuyingCustomer)
return
IF(HASONEVALUE(Customers[CustID])
    ,IF(FIRSTNONBLANK(Customers[CustID],0) in NonBuyingCustomer, 1, BLANK())
    ,BLANK()
)

[TomMartens]

Hey @Ashish_Mathur ,

please excuse, but I was asking for the DAX from @EVEAdmin  🙂

Nevertheless, I find it more readable if code snippets are used with the "Insert Code" option., but besides that there is no other reason.

Regards,
Tom

[EVEAdmin]

@TomMartens thank you

I will try as soon as time allows and update this thread.