Forum Discussion
RLS Working But Visuals Show Blank Until Location Manually Selected
Hi Power BI Community,
I'm experiencing an issue with Row-Level Security (RLS) where the security filtering works correctly, but visuals remain blank until users manually select a location from the slicer. I believe this is due to missing relationships, but I'm getting ambiguous path errors when trying to create them.
Current Setup:
- RLS is applied on VW_LOCATION table using [HasAccess] = 1
- Users can only see their assigned locations (RLS works correctly)
- Fact tables: FACT_SALES (sales data) and FACT_BUDGET (budget data)
- Both fact tables have LOCATION_KEY column
- Both fact tables connect to DIM_DATE (active relationships)
The Problem:
- When users log in with RLS, the location slicer shows a previously selected value (or is blank)
- All visuals show blank/no data until the user manually clicks their location in the slicer
- Once clicked, everything works perfectly - data appears and filters correctly
What I've Tried:
- Created relationship: VW_LOCATION[LOCATION_KEY] → FACT_SALES[LOCATION_KEY] (Many-to-one, Both directions)
- Attempted to create: VW_LOCATION[LOCATION_KEY] → FACT_BUDGET[LOCATION_KEY] (Many-to-one, Both directions)
- Get ambiguous path error: 'VW_LOCATION' -> 'FACT_SALES' -> 'DIM_DATE' and 'VW_LOCATION' -> 'FACT_BUDGET' -> 'DIM_DATE'
Current Relationships:
- VW_LOCATION → FACT_SALES (active, both directions) ✓
- FACT_SALES → DIM_DATE (active, both directions)
- FACT_BUDGET → DIM_DATE (active, single direction)
- FACT_BUDGET has NO relationship to VW_LOCATION (is this the problem?)
What I Need: A way to make the RLS filter from VW_LOCATION automatically propagate to both FACT_BUDGET and FACT_SALES so budget and sales visuals show data immediately without requiring manual slicer selection.
Questions:
- How can I resolve the ambiguous path error to create the location→budget relationship?
- Should I create a separate date table for budget data?
- Is there a way to use inactive relationships without having to modify dozens of budget measures?
- Is there a better model design pattern for handling multiple fact tables with RLS on a location dimension?
I'd prefer a model-level solution rather than modifying every measure with USERELATIONSHIP, as I have quite a few budget measures.
Any guidance would be greatly appreciated!
The data model is below:
Illutstration of issue. Screenshot 1
Screenshot 2:
Additional Note: It's always the last location value I would have done 'View As' for which appears selected in the slicer, but because of RLS conditions it shows no data until I manually click the correct location.
Optional addition if you want to be more specific:
Additional Context:
- Budget data is stored at monthly level in FACT_BUDGET
- Sales data is daily in FACT_SALES
- VW_LOCATION[LOCATION_KEY] contains distinct location IDs
- RLS role example: [Email] = USERPRINCIPALNAME()
- After creating the sales relationship, sales visuals work perfectly with RLS
- Budget visuals remain blank until location is manually selected from slicer
Thanks,
J
Hey nimishrao ,
I admit I didn't read it all, but already the problem is here:
The Problem:
- When users log in with RLS, the location slicer shows a previously selected value (or is blank)
- All visuals show blank/no data until the user manually clicks their location in the slicer
- Once clicked, everything works perfectly - data appears and filters correctly
If you save a report on default specific selection, every user will start from that point. Since you filter on location by RLS, just keep it without any selection, then it cannot happen that pre-selected location is different then user's assigned RLS one.
Also, please rebuild this:- FACT_SALES → DIM_DATE (active, both directions)
Both directions for the calendar is not great idea, you will lose all advantages of built in time intelligence, just keep it Date -> Fact One To Many , single.
Hi nimishrao ,
What’s happening here is pretty common the slicer can hang onto a saved selection that doesn’t match what the user’s RLS permissions allow, so the visuals show up blank at first. RLS only lets users see their permitted locations, so if the slicer is set to a spot they can’t access, nothing shows until they pick a valid location themselves. Easiest fix: save the report with no location selected in the slicer, so RLS handles what’s visible when someone opens the report.
Also, double-check your model relationships. Bidirectional filters on date tables can make things messy and unpredictable, especially when you’ve got multiple fact tables like sales and budget.
It’s usually best to keep the relationships one-way from the date table to the fact tables. To make sure sales and budget both follow the same RLS rule, connect them through a shared location dimension instead of linking each one separately to the RLS table. That way, you can apply RLS to the location dimension and filter both tables together.
If you can’t update the model just yet, you could use USERELATIONSHIP in your measures to temporarily activate the link between the RLS location table and the budget fact table. But for the long run, switching to a shared location dimension and single-direction relationships is the way to go. Once you’ve made those tweaks and cleared any slicer preselection, your visuals should load right away for users under RLS no extra clicks needed.
Thank you.
5 Replies
Hey nimishrao ,
I admit I didn't read it all, but already the problem is here:
The Problem:
- When users log in with RLS, the location slicer shows a previously selected value (or is blank)
- All visuals show blank/no data until the user manually clicks their location in the slicer
- Once clicked, everything works perfectly - data appears and filters correctly
If you save a report on default specific selection, every user will start from that point. Since you filter on location by RLS, just keep it without any selection, then it cannot happen that pre-selected location is different then user's assigned RLS one.
Also, please rebuild this:- FACT_SALES → DIM_DATE (active, both directions)
Both directions for the calendar is not great idea, you will lose all advantages of built in time intelligence, just keep it Date -> Fact One To Many , single.
Hi nimishrao ,
What’s happening here is pretty common the slicer can hang onto a saved selection that doesn’t match what the user’s RLS permissions allow, so the visuals show up blank at first. RLS only lets users see their permitted locations, so if the slicer is set to a spot they can’t access, nothing shows until they pick a valid location themselves. Easiest fix: save the report with no location selected in the slicer, so RLS handles what’s visible when someone opens the report.
Also, double-check your model relationships. Bidirectional filters on date tables can make things messy and unpredictable, especially when you’ve got multiple fact tables like sales and budget.
It’s usually best to keep the relationships one-way from the date table to the fact tables. To make sure sales and budget both follow the same RLS rule, connect them through a shared location dimension instead of linking each one separately to the RLS table. That way, you can apply RLS to the location dimension and filter both tables together.
If you can’t update the model just yet, you could use USERELATIONSHIP in your measures to temporarily activate the link between the RLS location table and the budget fact table. But for the long run, switching to a shared location dimension and single-direction relationships is the way to go. Once you’ve made those tweaks and cleared any slicer preselection, your visuals should load right away for users under RLS no extra clicks needed.
Thank you.
Thanks everyone appreciate it.
