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
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
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
We want your feedback!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
A Year in Review - December 2024
Find out what content was popular in the Fabric community during 2024.
