Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
arcegabriel
Helper I
Helper I

Dual relationship and filtering

(I just learned Power Bi two weeks ago - appreciate your patience)

Looking for some assistance

  1. I have a "Communications" table that includes line items for each communications from one PC to PC another. Each line includes date, originating and destination PC.
  2. I have a "flag" table that includes flags for special PCs. Say one of the flags is called "Lab"

I would like to be able to easily filter on my report and filter a line when a) originating b) destination c) originating and destination have the flag "Lab"

What I did on Power Query is to pull (merge) the flag on the communications table for each originating and destination and concatenate it. So I have resuls like (original flag, destination flag)

Lab, null

null, Lab

Lab, Lab

Lab, Office

Office, Lab

Office, Office

....

 

I concatenated this both on a single field and I can filter on a report but it is inconvenient because instead of me just selecting "Lab" I need to select many entries with the word Lab. 

Appreciate any suggestions to make this easier

 

PS. I could use to "flag" tables but then the report can not filter "OR" only "AND"

1 ACCEPTED SOLUTION

@arcegabriel ,

 

OK, I hope you're ready for this!

We're going to create this model:

BA_Pete_0-1626427197732.png

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

20 REPLIES 20
BA_Pete
Super User
Super User

Hi @arcegabriel ,

 

Do you want to be able to filter separately/specifically where the communication is LAB>LAB, or are you happy for this to just be one of the result set when you filter on 'LAB'?

 

To just have it in the result set, then you could try something like the following. I'm assuming that your Flags table is a unique list of all the different names:

flagNameExists = 
SEARCH(
  SELECTEDVALUE(flagTable[flagName]),
  SELECTEDVALUE(commsTable[pcConc]),
  -1
)

 

The [pcConc] field referenced is your concatenated field in he comms table.

 

You would then apply this measure as a filter on the visuals you want to be filtered by your flag slicer, with the logic: [flagNameExists] >= 1.

 

If you want to be able to identify specifically those comms that are from and to the same flag, then in your comms table you could create a new column, something like this:

if [originating] = [destination] then "Same" else "Different"

 

You can then add this field to another slicer to allow this characteristic to be filtered on.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




No, I just want to click "Lab" to consider any/all possibilities

@arcegabriel ,

 

Ok. I've updated my original answer with the options as I see them.

Let me know how you get on.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




The flag table has this form

PC Flag

PC1 Lab

PC3 Lab

PC1 Office (Note this is valid two flags for same item)

PC7 Office

etc

 

I would need to adjust your suggestion? 

 

@arcegabriel ,

 

Basically, for this part of the measure:

SELECTEDVALUE(flagTable[flagName])

you need a list of all the possible PC names (Lab, Office etc.) in a column.

 

What the measure's doing is checking whether there is a valid PC name in the list that matches any part of the text in your concatenated column. If there is, it outputs a number which is the character position in the concatenated text of where the PC name is found. That's why the filter logic is [_measure] >= 1. If there's a valid PC name in the concatenated text, the measure will ouput a position number that will be > 0, so the filter keeps it in the output.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@BA_Pete Thanks 

I understand the concept but not sure I understand how to pull it all together

Ultimately, I want to have on the visualization a filter called see all the options (generated from the data) and checkbox next to them

[] Lab

[] Office

[] Den

When I click on "Lab" I would like the data to be filtered in the manner I described (i.e. filter out anything that does not have "Lab" in the communications table as either/both origin or destination)

@arcegabriel ,

 

OK, so it sounds like we need a unique flag name list to get this to work.

In Power Query, create a new blank query and type this into the formula bar:

= Table.Distinct(Table.SelectColumns(theNameOfYourFlagTableQuery, "Flag"))

 

This should create you a distinct list of all the possible names from your flags table. Filter out any null values. Call this new query 'dimFlagName'.

Then update the measure as follows:

flagNameExists = 
SEARCH(
  SELECTEDVALUE(dimFlagName[Flag]), //I've updated this to reference the new distinct table
  SELECTEDVALUE(commsTable[pcConc]),
  -1
)

You would use this new dimFlagName[Flag] field in your report slicer.

Then follow the other steps as previously provided.

 

If this still doesn't work for you, then you may need to provide some of your actual data and/or your PBIX file so I can see what's going on with the actual data/model.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@BA_Pete Brilliant. Got it. Sorry for slowness just getting started with power bi. I did notice one small error on your formula (missing a comma - should be two before -1)

flagNameExists = 
SEARCH(
  SELECTEDVALUE(dimFlagName[Flag]), //I've updated this to reference the new distinct table
  SELECTEDVALUE(commsTable[pcConc]),,
  -1
)

Really appreciate it

@arcegabriel ,

 

Cool, happy it's working for you.

Great spot on the commas! 👍

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thanks, 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,

  1. 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
  2. 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 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@arcegabriel ,

 

OK, I hope you're ready for this!

We're going to create this model:

BA_Pete_0-1626427197732.png

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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 ,

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@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

 

  1. I have an slicer on commSearchTerm.
  2. commSearchTerm expands into commPatternCode.
  3. 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 

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Unfortunately it doesn't work. If I entered that formula on a column:
1. Table in PowerBI shows all Y

2. 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

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thanks Pete, the problem is that the measure does not let me enter TableB[commPatternCode]

(only accept measures) 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.