Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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?

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Anonymous ,

     

    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.

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Anonymous 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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_iddate (dd/mm/yyyy)item
      101/03/2023tomato
      101/03/2023pear
      101/05/2023tomato
      201/03/2023pear
      201/03/2023tomato
      201/05/2023pear
      301/03/2023tomato
      301/05/2023meat
      401/05/2023pear

       

      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's avatar
        v-jianboli-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi Anonymous ,

         

        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.