Forum Discussion
Advocate IIRe: Relationship filtering on many to one doesnt filter
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.
10 Replies
duggyI 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:
Userid SiteId 123 9 123 10 123 11 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:
SiteId Name 9 Site 1 10 Site 2 11 Site 3 12 Site 4 13 Site 5 14 Site 6 The goal is that when the multiplesites is filtered through managed role userid 123 then the sites table would filter the data to:
SiteId Name 9 Site 1 10 Site 2 11 Site 3 At the moment the sites table is not responding at all.
Much appreciated.
Seward12533Solution Sage
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- duggy
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?
v-danhe-msftMicrosoft Employee
Hi duggy,
From your data structure, it could work on my side:
Sample data:
Result:
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.
Regards,
Daniel He
- duggy
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.