List of customers with no orders, based on a slicer

[Anonymous]

I have a list of customers, who have to make a purchase every time there is a new campaign.

Also, I have data that describes the purchases and who made them.

The task is to provide a list of customers, who have not made a purchase. This list has to change in line with a campaign slicer. In other words, I have to provide a list of customers, who have not made a purchase for every campaign conducted.

Any ideas on how to implement this?

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please do like this.

1. Delete the relationship.

2. Create a measure.

Measure = 
VAR x = 
CALCULATE(
    MAX(Purchases[Campaign]),
    FILTER(
        Purchases,
        Purchases[Countries] = MAX(Countries[Countries])
    )
)
RETURN
IF(
    x = BLANK(),
    "No purchases",
    x
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

(Customers are changed to countries here)

Just to reiterate, I have to compare the column Countries from Purchases table to the complete list of countries in Countries table. I have to do it by campaigns. For instance, for campaign "1" nothing has to be provided, as there are purchases for all of the countries. However, campaign "2" has missing countries, and consequently, the list of them has to be displayed.

I have many campaigns in the data, the same logic has to be applied to all of them.

I see it as two visuals:
1) a slicer, based on campaigns
2) a table, that has a list of countries with no purchases, which changes every time the slicer is changed.

[Anonymous · Accepted solution]

Also, this page helped very much:

https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please do like this.

1. Delete the relationship.

2. Create a measure.

Measure = 
VAR x = 
CALCULATE(
    MAX(Purchases[Campaign]),
    FILTER(
        Purchases,
        Purchases[Countries] = MAX(Countries[Countries])
    )
)
RETURN
IF(
    x = BLANK(),
    "No purchases",
    x
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

https://wetransfer.com/downloads/5114656cf729d46514383ac1774a9ef820200929143951/337b595fe242ce634a25...

[Anonymous]

@amitchandak 

[amitchandak]

@Anonymous , You have do try something like this

if(isblank[sales], customer[ID],blank())

or

countx(values(customer[ID]),if(isblank[sales], customer[ID],blank()))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.