Filtering by Related Tables (Include and Exclude)

[Chaucer]

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

[amitchandak]

@Chaucer , Can you share sample data and sample output in a table format?

Most of thing should be possible using page level or visual level filters

Relative date filter : https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

[Chaucer]

Hi @amitchandak 

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

Customers
Customer Email	Customer GUID
harry.potter.com	1
hermione.granger.com	2
tom.riddle.com	3
severus.snape.com	4
ron.weasley.com	5

Orders
OrderNo	Customer GUID	Margin
1	1	15%
2	3	25%
3	4	15%
4	5	25%
5	2	15%
6	2	25%
7	1	15%

Order Lines
Orderline	Order No	Order Date	SKU
1	1	01/01/2020	Cat
2	1	01/01/2020	Bird
3	2	01/02/2020	Cat
4	2	01/02/2020	Dog
5	3	01/03/2020	Goldfish
6	4	01/04/2020	Bird
7	4	01/04/2020	Dog
8	5	25/04/2020	Goldfish
9	6	01/05/2020	Cat
10	7	01/06/2020	Dog
11	7	01/06/2020	Goldfish

SKU Details
SKU	Department
Cat	Mammal
Bird	Bird
Dog	Mammal
Goldfish	Fish

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]

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]

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!