Filter SO contact by campaign list

[ericleung]

I have a campaign list with ( startdate,enddate,shopname) in the campaign table. 

I need to filter the order contacts list fulfill all the campaigns in the list.

Any idea how to use the table list as the filter criteria?

[samdthompson]

have you related your campaign list with your campaign table?

[ericleung]

No campaign list relationship,

maybe I need to show the scenariao in details and examples.

The dataset1 (SO with contact ID) 

Order ID	Date	Contact ID	Shop
A0001	1/1/2015	A	001
A0002	6/1/2013	B	001
A0003	6/10/2014	D	001
A0004	5/19/2013	A	002
A0005	7/1/2012	B	002
A0006	4/1/2015	C	004
A0007	5/30/2015	C	005
A0008	4/16/2012	E	003
A0009	5/17/2016	E	003
A0010	6/16/2014	E	003
A0011	7/2/2013	F	002

Filter Criteria is in a DataSet (Campaign List)

Shop No	Shop name	From	To
001	1	6/1/2012	6/30/2012
002	2	7/1/2013	7/31/2013
003	3	6/1/2013	6/30/2013
001	1	7/1/2012	7/31/2012
002	2	8/1/2013	8/31/2013
003	3	7/1/2013	7/31/2013

we need to use the campaign list as the SO filter criteria to filter out the so contact list. so the expected result should be

B

F

[v-sihou-msft]

@ericleung

In this scenario, you can merge these two tables in Query Editor and then using DAX formula to judge if the date in SO table is in the scope of dates marked in Compaign List. Please refer to following steps.

1. Select “SO with contact ID” in Query Editor and click ‘Merge Queries’ in the top right corner. Select ‘Campaign List’ table and matching columns to create a merged table. Expand the ‘NewColumn’ as below.
   
2. Create a column in “SO with contact ID” to judge if the date is in the scope of dates between From and To.
   

   Filter_Mark = 
   IF (
       'SO with contact ID'[Shop] = 'SO with contact ID'[NewColumn.Shop No],
       IF (
           'SO with contact ID'[Date] >= 'SO with contact ID'[NewColumn.From]
               && 'SO with contact ID'[Date] <= 'SO with contact ID'[NewColumn.To],
           TRUE (),
           FALSE ()
       ),
       FALSE ()
   )
   

3. Create a measure to display the expected results.
   

   Filter_Result = 
   CONCATENATEX (
       FILTER ( VALUES ( 'SO with contact ID' ), 'SO with contact ID'[Filter_Mark] = TRUE () ),
       'SO with contact ID'[Contact ID],
       ", "
   )
   

4. Drag a Card chart into your canvas to show the results.