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 group by another selection of filters.

 

So, for instance, using our tables below we'd like to produce a list of customers with the following inclusive filters:

Order Lines SKU = x or y

Order Lines OrderDate = last year

Orders Margin % >20%

 

and then reduce that list of customers by any customer who fit the following filters:

 

Max Orders Order Date = last two weeks

and/or

Order Lines SKU = z

 

Pointers gladly taken, even if it's just to point me at an appropriate resource!

TIA

 

 

Need Help

4 Replies

    • 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!