Dual relationship and filtering

arcegabriel ,

OK, I hope you're ready for this!

We're going to create this model:

Each of these tables are related on [commPatternCode].

dimCommsBridge is just there to avoid MANY:MANY relationships.
Everything we do will be in Power Query initially, with just one DAX measure at the end.

***Preparing your fact table (I will refer to this as factComms):
-- I am assuming that your flag table only has one row per PC (but may have duplicated flag names).
1) Merge your flag table onto your fact table on flagTable[PC] = factComms[origination], expand Flag field and change the name to flagOrig.
2) Do the same on flagTable[PC] to factComms[destination], expand and rename to flagDest.
-- You should now have two new columns in your fact table that give you the flag names of the PC's involved in each row.
3) Create a new column in factComms by merging [flagOrig] and [flagDest] together using '-' as the delimiter.

-- You should now have a new column in factComms that looks something like this in each row: Office-Lab. Call this [commPatternCode].

***Creating dimComms:
4) Create a blank query and, in the formula bar, type this:

= Table.Distinct(Table.SelectColumns(factComms, {"flagOrig", "flagDest"}))

to give you a table with all unique combinations of orig/dest from factComms.
5) Filter out nulls from both columns if necessary.
6) Add a new [commPatternCode] column in dimComms by merging your two columns together as you did before.
7) Add another new column in dimComms called [commSearchTerm], using this code:

{[flagOrig], [flagDest]}

-- Note the use of curly braces here!

😎 Expand this column TO NEW ROWS.
9) Remove any other columns keeping only [commPatternCode] and [commSearchTerm] and change data types to text.

***Creating dimCommsBridge
10) Create a blank query and type this in the formula bar:

Table.Distinct(Table.SelectColumns(dimComms, "commPatternCode"))

11) Apply these new tables to your model and relate as per my initial screenshot.

-- Take care to notice that dimComms > dimCommsBridge filters in BOTH directions.

12) Now create a measure in your factComms table like this:

_countFilter = COUNTROWS(factComms)

-- Relate any other dimension you want directly to the fact table, use dimComms[commSearchTerm] in your single-word slicer, and add the [_countFilter] measure as visual-level filters to any slicers etc. that you want to react to filter changes, with the logic [_countFilter] > 0.

Voila!

Pete