Forum Discussion
Helper IDual relationship and filtering
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
Super User
Helper IThanks, just run into a related issue. When using along with other sliders I get very strange results
Right now I have
Slicer 1 with Flag
Visual 1 filtered with Flagnameexists
Slicer 1 does what is supposed to do
I have other slicers which are not working very well. Normally slicers will trim down the selection to only show relevant items.
Those other slicers, oddly,
- Show an unconstrained selection set (i.e. shows lines that were filtered on the Visual 1. When I click on something it does have the intended effect
- If I Apply Flagnameexists as a filter on the slicer it removes many items including items that should stay on the selection (removes too much)
I hope that made sense
Appreciate any sugestions
- BA_Pete
NOTE:
You will need to ensure that every PC featured in your fact table has a corresponding entry in your flags table, even if the flag is just something like "No Flag" or similar. Any time a new PC comes online and communicates with another, thus creating a new row in your fact table, you will need to ensure that the new PC has an entry in your flags table.
Also, sorry that MS decided to change '8 )' to a sunglasses emoji, lol.
Pete
- BA_Pete
Awesome! Glad it's worked out for you.
You're absolutely right to change the dim source to a smaller table. I didn't give it much thought in the pursuit of a quick solution, but happy that you've taken it as intended and improved it for your specific scenario.
One thing: could you mark the main step-by-step answer as the solution please? This will make sure that it's that answer which shows up as the correct answer in search results, rather than my NOTE answer which won't make much sense to others looking for help on the same topic. Feel free to 'un-solution' (definitely a real word :D) the NOTE answer afterwards if you wish.
Have a good weekend!
Pete
- BA_Pete
Hi arcegabriel ,
1) This is to do with how your model is set up and which tables affect which when filters/slicers are applied. The solution I've given you is very specific to just a single visual and does not perform any table filtering behind the scenes, therefore your slicers will not dynamically adjust to any selections.
2) The _flagNameExists measure will definitely not work correctly when applied to anything other than a slicer/visual that contains the commsTable[pcConc] field, as this is the only field that the measure is concerned with.
I'm having a think now about how to solve all the issues structurally, but also dynamically, so it stands the test of time.
I'll post again later with how I get on.
Pete
- BA_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
- arcegabriel
Ok, absolutely amazing. One important thing to note that I initially missed (and you clearly stated) was to make the bi-directional filter
Really appreciate it.
One small update (more related to my data) is that after testing, I decided to generate my dim tables from a separate table with flags and not my fact table. My fact table has millions of lines and I noticed it would try to load those millions once when processing the fact table, another time for the dimCommsBridge and once more for dimComms.
- arcegabriel
BA_Pete Coming back to this after a year. I have been successfully using your method all this time! I was trying to make an improvement but unfortunately I am stuck after a few hours 😳.
Right now I have the setup you proposed
Table dimComms
Table dimCommsbridge
factComms
They work together to filter a single variable whether this variable appears in either or both columns in factComms
- I have an slicer on commSearchTerm.
- commSearchTerm expands into commPatternCode.
- commPatternCode then filters factComms
All good, filter works.
What I am trying to improve my visual.
If I have selected commSearchTerm="x" through slicer, then
commPatternCode is of the form {"x",anything}, {anything,"x"}, {"x","x"}
The visual is based on factComms, I would like to add two new column called originationformat and destinationformat.
originationformat = Y if commPatternCode is either {"x",anything}, {"x","x"} otherwise = N
destinationformat = Y if commPatternCode is either {anything,"x"}, {"x","x"} otherwise = N
Unfortunately can not figure out a way to make this work even trying selectedvalue function.
Appreciate if you have any tips
- BA_Pete
Hi arcegabriel ,
I think you could use the FIND function in DAX, something like this:
_originationFormat = IF( FIND( SELECTEDVALUE(tableA[commSearchTerm]), TableB[commPatternCode] ) > 1, "N", "Y" )You'd just switch around the "N" & "Y" for the destinationFormat variant.
https://learn.microsoft.com/en-us/dax/find-function-dax
Pete
- arcegabriel
Unfortunately it doesn't work. If I entered that formula on a column:
1. Table in PowerBI shows all Y2. Table visual in PowerBI shows all Y
On reading sounds like SELECTEDVALUE can be used on measures but not on columns. If I create a measure for selectevalues and put that measure on a card, I do see it update
- BA_Pete
Yes, it's meant to be a measure, not a column. Sorry, I should have been clearer.
In order to be dynamic based on the slicer selection, it HAS to be a measure. Columns aren't updated after the intial report load.
Pete
- arcegabriel
Thanks Pete, the problem is that the measure does not let me enter TableB[commPatternCode]
(only accept measures)