Identify missing values and allowing filters

[JaysYee]

Hi Everyone,

I have two tables.  One is the reference table that has all the possible categories.  See example below.

Table 1

Sector	Subsector	Group	Subgroup	Key (concatenation of Group and Sub-Group separated by ": ")
Sector 1	Subsector A	Group 1	Subgroup 1	Group 1: Subgroup 1
Sector 1	Subsector A	Group 2	Subgroup 2B	Group 2: Subgroup 2B
Sector 1	Subsector A	Group 3	Subgroup 3A	...
Sector 1	Subsector B	Group 2	Subgroup 2A	...
Sector 1	Subsector B	Group 2	Subgroup 2B	...
Sector 2	Subsector C	Group 2	Subgroup 2C	...

Table 2 (Ref. Table)

Group	Subgroup	Key (concatenation of Group and Sub-Group separated by ": ")
Group 1	Subgroup 1	Group 1: Subgroup 1
Group 2	Subgroup 2A	Group 2: Subgroup 2A
Group 2	Subgroup 2B	...
Group 2	Subgroup 2C	...
Group 3	Subgroup 3A	...
Group 3	Subgroup 3B	...

My objective is to identify in Table 1 which subgroups are missing for either at the sector or subsector level (e.g., using filters).  For example when I filter for Subsector B I should receive a list of values containing:

Subgroup 1

Subgroup 2C

Subgroup 3A

Subgroup 3B

When I filter for Sector 1 I should receive a list of values containing:

Subgroup 2C

Subgroup 3B

I don't have preference regarding using DAX or Power Query.  In DAX I set two variables (one for Table 1 and the other for Table 2) and then used "EXCEPT" in my RETURN, but I'm getting blanks when applying the filter.  The Key column are related between the two tables.

Thank you for your help!

[amitchandak]

@JaysYee , Create a new table with

Join on key to both table

CombinedSectors =
UNION(
Sumamrize(
(Table1),
Table1[Sector],
Table1[Subsector],
Table1[Key]
),
Sumamrize(
(Table2),
Table2[Sector],
Table2[Subsector],
Table2[Key]
)
)

Use the key from this common table and visual 

and have measures like

countrows(except(Table2[Key], Table1[Key]))

[JaysYee]

Thank you for your response!

I may be mistaken.  Doesn't UNION append the summarized Table2 to Table1?  Also I can't see how I can use EXCEPT with this new table "Combined Sector."  Additionally, there are no sectors or subsectors in my Table 2.