Forum Discussion
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...
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!
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!