Alternative State to select 2 different time ranges. Some data dropped off after link to different table

[Gavin_FBu]

Alternative State to select 2 different time ranges so I can show the Sales for Range A and Range B then calculate  the sales variance in the sames table.   It is working fine until the raw data link to another table, some products started to dropped off.

Two Tables in the apps. 
1) Sales Table (contained transaction sales data)

Date	Store	Product	Sales
1/08/2022	ABC	Apple	123
2/08/2022	ABC	Apple	54
3/08/2022	ABC	Orange	78
4/08/2022	ABC	Banana	124

2) Campaign Table (contain campaign ID and products involved in the campaign)

Campaign ID	Product
1	Apple

My First step is only use Sales Table to create the following dashboard:

Alternative State for both Date Range Filter (I called it RangeA and RangeB)
A Sales = sum({RangeA<[Store]=$::[Store], [Product]=$::[Product]>}Sales)
B Sales = sum({RangeBA<[Store]=$::[Store], [Product]=$::[Product]>}Sales)

This is working fine for [Store] as a filter and I can calculate the variance without issue. 

My second Step would like to included campaign ID (from second table) as a filter so user can select the Campaign ID and the table will filter to the relative products.  And it looks like this:

A Sales = sum({RangeA<[Store]=$::[Store], [Product]=$::[Product], [Campaign ID]=$::[Campaign ID]>}Sales)
B Sales = sum({RangeB<[Store]=$::[Store], [Product]=$::[Product], [Campaign ID]=$::[Campaign ID]>}Sales)

Once I done the formula included [Campaign ID], table dropped off all the products that do not existed in Campaign table.  How do I included every product from the sales table and use campaign ID just purely for filtering product?

Thanks
Gavin

[jwjackso · Accepted solution]

Create an alternate state for the [Campaign ID] field.  Use an intersection of  P()  functions in the formula below:

A Sales = Sum({RangeA<[Store]=$::[Store],[Product]=P({$}[Product])*P({Campaign}[Product])>}Sales)

B Sales = Sum({RangeB<[Store]=$::[Store],[Product]=P({$}[Product])*P({Campaign}[Product])>}Sales)