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
duggy
Advocate II
Advocate II

Relationship filtering on many to one doesnt filter

Hi,

 

I have several tables all linked to each other. What I am finding is that the filtering from many to one doesnt respond. If I filter on the one to many side then the many table information changes. However if I filter on the reverse from the many side then the one table is not impacted (using bi-directional filtering).

 

relationships.JPG

A user is assigned to multiple sites. The users table contain the user information and the multiplesites table is the table that contains the userid and the siteid that user is assigned to:

So user 123 can be assinged to site 1, 2, 5 and 6 for example.

When I filter on the sites table then the multiplesites table filters and so does the daily table.  But when I filter on the daily or on the multiplesites table the filtering doenst activate. I.e. when I filter on the many to one direction (initially the daily to sites was also bi-directional) it doesnt filter the one site, just the many side of the relationship.

 

Any ideas?

Much appreciated.

1 ACCEPTED SOLUTION

Ok, update, cause I think that I have bothered enough.

 

The solution that worked was to select the "Apply security filter in both directions" options on the relationship. I swear I did this before and it didnt work but for now it does.

View solution in original post

13 REPLIES 13
v-danhe-msft
Employee
Employee

Hi @duggy,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Seward12533
Solution Sage
Solution Sage

It has to do with filter context.  The filter context wont' flow against the arrows of your 1:M relationships. You need to explicitly define the context in your measures. If your not writing measures but letting the visual define it for you by just dragging in the column you  will need to define explicit meausres and then force the filter context.  

 

You can do this a variety of methods  see the tutotial I put together. 

 

https://1drv.ms/u/s!AuCIkLeqFmlhhJgzLZ6jPcWgLGu3Yw

Thank you for the file, am busy looking through it and trying to implement.

Just to clarify, this is the solutoin that when I select a user in the multiple table (multiplesites) then the single table (sites) will only show the sites that the user is assigned to (from the multiplesites table that has the userid and the siteid).

 

When I do this graphically in powerbi charts they seem to respond correctly (select the user, the list of the sites the user is assigned to is displayed and the totals for the sites is displayed).

 

I am however passing the userid through Managed Roles. So that when the graphs are embedded the logged in user can only see his data for his sites.

 

We still ont he same page and your solution will correct for this?

 

Much appreciated.

I am not sure that this is what I am looking for, or possibly I am not understanding it.

The simple issue that I am having is when I select the user in Manage Roles for the mutiplesite, I see the filer of data for all the sites the user is associated with.

 

So if user 123 is associated with sites 9, 10, 11 then the multiplesites table looks like:

 

UseridSiteId
1239
12310
12311

 

I expect that when the managed roles user of 123 is selected then this is the result (this is indeed happening in the multiplesites table)

However in the sites table, the filter is not carrying through. I have a sites table with:

 

SiteIdName
9Site 1
10Site 2
11Site 3
12Site 4
13Site 5
14Site 6

 

The goal is that when the multiplesites is filtered through managed role userid 123 then the sites table would filter the data to:

SiteIdName
9Site 1
10Site 2
11Site 3

 

At the moment the sites table is not responding at all.

 

Much appreciated.

 

Hi @duggy,

From your data structure, it could work on my side:

Sample data:

1.PNG

Result:

4.PNG

I would suggest you test again. If issue persists, would you please share your pbix file if possible? Also you can test with our sample report on your side to see if the same issue occurs.

https://www.dropbox.com/s/l8ww3iul1av80w2/Relationship%20filtering%20on%20many%20to%20one%20doesnt%2...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ok, I see the issue you have. Change the data to as follows (and you will see the issue that I am having):

on table multiplesites change the user 124 to have siteid 9 and 10. Meaning that user 123 is assigned to site 9, 10 and 11 and user 124 is also assigned to 2 of the 3 sites user 123 is assigned to (a subset of the same sites) - sites 9 and 10.

 

Then you will have the same issues that I am having which I need to try solve for.

 

This means that userA can be assigned to site 1,2 and 3 and userB can also be assigned to sites 1,2 and 3. At the moment if they are all assigned to different sites it will work, but if they are assigned to same sites or subset of sites it doesnt filter through.

 

The only way I was able to solve this (a VERY ugy and repettive way) was to merge the sites and the multiplesites table based on sitesid. This means the sites table now has multiple rows for each site (a row for each userid).

 

So a site that is allocated to 10 users (where the sites table had one row) now the sites table has 10 rows each with a different user, very ugly way of doing it. Need to undo it and look at something proper.

Ok, update, cause I think that I have bothered enough.

 

The solution that worked was to select the "Apply security filter in both directions" options on the relationship. I swear I did this before and it didnt work but for now it does.

Thankyou for coming back to report this

Great, glad to hear.  Note there are many situatation where you cannot apply crossfiltering in both directions or not enough of them so I would recommending learning how to force crossfiltering for specific measures.  I can't access drop box from work so I can't demonstrate in the solution provided by @v-danhe-msft 

It’s the filter context. The relationship is from Sites -> DailyEntrance is sites is NOT normally filtered by daily entrance.

If the measure your displaying is [measure] try
NewMeasue = CALCULATE([measure],sites)

This will force that relationship to be applied.  

If it’s not working you may need to force a relationship with a different or additional tabales, or you coudl build and share a sample PBIX file.

See this updated example with a more complex relationship that more closely mirrors yours. https://1drv.ms/u/s!AuCIkLeqFmlhhJgzLZ6jPcWgLGu3Yw

Hi,

 

Thank you for the response. I am not sure I follow you. Sites to dailyentrance filtering is working. When I filter sites then the dailyentrance is filtering correctly.

 

Multiplesites to sites is not filtering correctly. When I Manage Roles to multiplesites by user (then the siteid remains for the given user) then Sites is not filtering.

 

Does this mean that I need to add something like (INTO SITES):

NewMeasue = CALCULATE(sites[siteid],multiplesites[siteid]) ?

in order to filter sites by the filtered siteid in multiplesites?

 

Please post an example.

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.