Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
have you related your campaign list with your campaign table?
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
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.
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 () )
Filter_Result = CONCATENATEX ( FILTER ( VALUES ( 'SO with contact ID' ), 'SO with contact ID'[Filter_Mark] = TRUE () ), 'SO with contact ID'[Contact ID], ", " )
User | Count |
---|---|
98 | |
91 | |
84 | |
72 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |