Here is my approach.
Set 2nd data as primary data source.
And duplicate 1st data source.
One is for P category and make relationships with Category and P category
Another is for S category and make relationships with Category and S category.
Then put formula as below.
[Category Lookup 1]
ifnull(ATTR([Primary P Category].[P Category]),ATTR([Primary S Category].[S Category]))
Also put P category from P category data source and S category from S category data source.
Then hide from Header.
2 data sources SM_9.3.twbx 30.9 KB
Thank you so much for your reply. This gives me a lot to think about. I see what you are doing with joining both the P set and the S2 set to the new "primary" S.
Unfortunately, I'm not sure this is the best solution for my situation. In my scenaria, the primary data source has a lot more fields and needs to remain as the primary.
Thanks for including the tblx file. I will study it further and see if it can help me create the final solution.
Figured it out. Turned out to be pretty simple. (It always is once you find the answer.)
Data Source 1 (Primary)
Data Source 2 (There can be many subcategories per Category)
The categories in data source 2 are used in both primary and secondary category fields in data source 1.
Given a sub-category, I want to filter items in Data Source 1 where the sub-category is in the Primary Category OR the Secondary Category.
Create a third data source using Data Source 2 (we'll call it Data Source 2A)
Blend primary to Data Source 2 on Primary Category = Category
Blend primary to Data Source 2A on Secondary Category = Category
Create a Parameter called SubCatParm based on [Data Source 2].[Sub-category] that contains all of the sub-category values. Make this parameter visible on the sheet.
Create a calculated field in Data Source 2 called InSubCat2. Formula: [Sub-category] = [SubCatParm]
Create a calculated field in Data Source 2A called InSubCat2A. Formula: [Sub-category] = [SubCatParm]
Create a calculated field in Data Source 1 called ItemInSubCat. Formula:
max([Data Source 2].[InSubCat2])=TRUE
max([Data Source 2A].[InSubCat2A])=TRUE
Filter sheet on ItemInSubCat = TRUE
When you select a Sub-cat value for SubCatParm, the secondary source calculated fields update to true or false accordingly. ItemInSubCat returns True if the Sub-category selected is related to either the primary or secondary category.