Forum Discussion

Chaucer's avatar
Chaucer
Helper II
6 years ago

Filtering by Related Tables (Include and Exclude)

I'm trying to make tool for my marketing department to produce a customer mailing list. The marketing team would select a number of filters to determine a target group, and then thin that target g...
Need Help
  • Chaucer's avatar
    Chaucer
    Helper II
    6 years ago

    Hi amitchandak 

     

    I've had a go at knocking out some sample data 🙂

     

    Customers 
    Customer EmailCustomer GUID
    harry.potter.com1
    hermione.granger.com2
    tom.riddle.com3
    severus.snape.com4
    ron.weasley.com5

     

    Orders  
    OrderNoCustomer GUIDMargin
    1115%
    2325%
    3415%
    4525%
    5215%
    6225%
    7115%

     

    Order Lines   
    OrderlineOrder NoOrder DateSKU
    11 01/01/2020 Cat
    21 01/01/2020 Bird
    32 01/02/2020 Cat
    42 01/02/2020 Dog
    53 01/03/2020 Goldfish
    6401/04/2020Bird
    7401/04/2020Dog
    8525/04/2020Goldfish
    9601/05/2020Cat
    10701/06/2020Dog
    11701/06/2020Goldfish

     

    SKU Details 
    SKUDepartment
    CatMammal
    BirdBird
    DogMammal
    GoldfishFish

     

    We want filters that would, in this example, provide the emails of customers who have ordered Mammals in last year, with a Margin above 20%. Exclude any customers who Ordered in the last 3 months, or who ordered a cat.

     

    Answer:

    Email
    tom.riddle.com
    ron.weasley.com

     

    I suspect that's 100% not how one is supposed to knock up trial data!

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      6 years ago

      Hi  Chaucer ,

       

      Create a measure as below:

      Measure = 
var _sku=CALCULATETABLE(VALUES('SKU Details'[SKU]),FILTER(ALL('SKU Details'),'SKU Details'[Department]=SELECTEDVALUE('SKU Details'[Department])&&'SKU Details'[SKU]<>"Cat"))
var _ordernumber=CALCULATETABLE(VALUES('Order Lines'[Order No]),FILTER(ALL('Order Lines'),'Order Lines'[SKU] in _sku&&DATEDIFF('Order Lines'[Order Date],TODAY(),MONTH)>3))
var _customerguid=CALCULATETABLE(VALUES(Orders[Customer GUID]),FILTER(ALL('Orders'),'Orders'[OrderNo] in _ordernumber&&'Orders'[Margin]>0.2))
var _email=CALCULATETABLE(VALUES(Customers[Customer Email]),FILTER(ALL(Customers),'Customers'[Customer GUID] in _customerguid))
Return
IF(MAX('Customers'[Customer Email]) in _email,MAX('Customers'[Customer Email]),BLANK())

      And you will see:

      For the related .pbix file,pls see attached.

       

       
      Best Regards,
      Kelly
      Did I answer your question? Mark my post as a solution!
      • Chaucer's avatar
        Chaucer
        Helper II
        6 years ago

        Hi Kelly,

        That's magic, thank you.

        Bear with me I'm currently getting a "Current version is out of date" error when trying to open your .pbix. despite a re-install... Any idea what version of PBI you're running? Haven't managed to do a proper investigation from online!

        Cheers!