Tricky task: is it possible to create a negative filter + positive filter?

[drav11]

Hi all,

I'm not sure if this task is possible to do it on Power BI.

I have a sales database with customer_id, date, product. I want to create a visual table where the user can extract a list of customers according to the visual filters of his choice. These filters are the date and the product purchased. In particular, the user can choose a range of dates in which the customer bought product X and another range of dates in which he did not buy product Y.

Example: list of customers who bought pears and tomatoes in March-23 , but who have not bought meat on May-23. Taking into account the large amount of input data, do you think it is possible to create such a dashboard?

[Greg_Deckler]

@drav11 Should be possible using something like a Complex Selector. Also, new and returning customers have a similar pattern. 

The Complex Selector - Microsoft Fabric Community

Better Sales from New Customers - Microsoft Fabric Community

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[drav11]

Thanks for the answer, I'll try to insert an example here.

I have two imported table:

1) customer_base: all registered customers (not all have made purchases)

customer_id

1

2

3

4

2) transaction: table with all transaction of customer_id:

customer_id	date (dd/mm/yyyy)	item
1	01/03/2023	tomato
1	01/03/2023	pear
1	01/05/2023	tomato
2	01/03/2023	pear
2	01/03/2023	tomato
2	01/05/2023	pear
3	01/03/2023	tomato
3	01/05/2023	meat
4	01/05/2023	pear

The visual table will have to provide the list of customers who bought pears or tomatoes in March, but didn't buy meat in May, therefore:

customer_id

1

2

[v-jianboli-msft · Accepted solution]

Hi @drav11 ,

Please try:

First create two new table:

Positive = 
var _a = DISTINCT('transaction'[date (dd/mm/yyyy)])
var _b = DISTINCT('transaction'[item])
return CROSSJOIN(_a,_b)

Negative = 
var _a = DISTINCT('transaction'[date (dd/mm/yyyy)])
var _b = DISTINCT('transaction'[item])
return CROSSJOIN(_a,_b)

Then create slicers:

Then apply the measure to the visual's filter:

Flag = 
var _a = SELECTCOLUMNS('Positive',"date",[date (dd/mm/yyyy)])
var _b = SELECTCOLUMNS('Positive',"item",[item])
var _c = SELECTCOLUMNS('Negative',"date",[date (dd/mm/yyyy)])
var _d = SELECTCOLUMNS('Negative',"item",[item])
var _e = SELECTCOLUMNS(FILTER(ALL('transaction'),[date (dd/mm/yyyy)] in _a&&'transaction'[item] in _b),"id",[customer_id])
var _f = SELECTCOLUMNS(FILTER(ALL('transaction'),[date (dd/mm/yyyy)] in _c&&'transaction'[item] in _d),"id",[customer_id])
var _g = EXCEPT(_e,_f)
return IF(SELECTEDVALUE(customer_base[customer_id]) in _g,1)

Final output:

Best Regards,

Jianbo Li

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

[drav11]

Thank You Jianbo,

It's work, do you think the dashboard is sustainable with a large amount of data?

[v-jianboli-msft]

Hi @drav11 ,

This is a question to which it is difficult to give a definitive answer.
It is difficult to have a specific criteria to define large amount of data.
This requires a case-by-case analysis. The calculations involved in this solution are not complex and usually will not cause problems.

If your problem has been solved, kindly Accept it as the solution. More people will benefit from it.

Best Regards,

Jianbo Li

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