Forum Discussion
ByPass RLS with Disconnected Table - COMPLEX ISSUE HELP
So, I have a really complex issue I am trying to resolve and hoping the community can assist with it, or tell me it is not even possible so I stop spinning my wheels on it….High Level, I have a relatively simple data model.
- I have 4 tables, dimfacility, factsales, dimdate, CompDataSet
- Dimfacility JOINS to factsales on Facilitykey
- Factsales JOINS to dimdate on saledate = date
- CompDataSet is a disconnected table (only joins to date table)
- Dimfacility contains facilitykey, facilityname, Prior Year Sales, Prior Year Qty and some other fields that are not relevant to this.
- Fact sales contains saledate, facilitykey, sale Amount, Quantity
- Compdataset contains facilitykey, salesdate, prior_year_sales, prior_year_qty
- My compdataset table is disconnected because we need it to bypass RLS when a user logs in so that we can see our selected facility (RLS Facility) compared to their relative comps.
Steps 1-5 are straightforward and I have that completed. I can see all of the comps when I log in and RLS is applied. The issues I am trying to resolve come in now. I am hoping the community can help with this, or just tell me that this is not possible in Power BI…….Here is very dumbed down sample data:
| DimFacility (RLS Applied already to single facility) | |||
| Facilitykey | FacilityName | Prior Year Sales | Prior Year Qty |
| 123 | FacilityABS | 157,000.00 | 2,400.00 |
| factsales (RLS already applied) | |||
| Facilitykey | salesdate | sales_amount | quantity |
| 123 | 1/1/2024 | 50,000.00 | 800.00 |
| 123 | 5/5/2024 | 7,000.00 | 300.00 |
| 123 | 7/5/2024 | 25,000.00 | 400.00 |
| 123 | 10/1/2024 | 75,000.00 | 1,400.00 |
| Compdataset (disconnected and bypasses RLS) | |||
| facilitykey | salesdate | prior_year_sales | prior_year_qty |
| 255 | 12/31/2024 | 160,000.00 | 2,200.00 |
| 564 | 12/31/2024 | 28,000.00 | 500.00 |
| 458 | 1/1/2024 | 150,000.00 | 2,350.00 |
- I have prior year sales in my dimfacility table (calculated in SQL query). To get true comps I need to be able to narrow down my CompDataSet where the Prior_Year_Sales in my comp data (calculated in SQL by facilitykey) are within +/- 10,000 of my RLS filtered facility.
- So, for example, if my RLS Facility had 20,000 in sales last year (dimfacility;Prior Year Sales) then my CompDataSet should flag only the facilities that had Prior_Year_Sales between 10,000 and 30,000.
- I would need to repeat the same logic as #6 and #7 but for quantity, +/-100.
- I have a measure that is flagging these correctly in its current state (measure that just outputs a 1 or 0), but I need to be able slice my report and choose “Show Sales Comps”, “Show Qty Comps” or be able to select both to show only comps that fit into both criteria.
Here is what I would expect results to be. Not necissarily in the table, but logically we should see this:
| Compdataset (disconnected and bypasses RLS) | My Own Flags (not in PBI) | ||||
| facilitykey | salesdate | prior_year_sales | prior_year_qty | Sales Comp | Qty Comp |
| 255 | 12/31/2024 | 160,000.00 | 2,200.00 | Show Sales Comp | Not Qty Comp |
| 564 | 12/31/2024 | 28,000.00 | 500.00 | Not Sales Comp | Not Qty Comp |
| 458 | 1/1/2024 | 150,000.00 | 2,350.00 | Show Sales Comp | Show Qty Comp |
Is this something that is even possible with the way we are using the disconnected table and Row Level Security?
I have tried to ask Claude, ChatGPT and have been trying my own solutions now for a few days. I figured if it was possible in any way at all, the community would be able to help!
As always, I appreciate you all.
Thanks
Ryan F
You can build tables on the fly inside measures, particularly now that you can use SUMMARIZECOLUMNS inside a measure.
So you could use SUMMARIZECOLUMNS to store a table in variable, using the measure definitions above to get the correct numbers, use FILTER on that table variable to apply any additional filters, and then perform an AVERAGEX over that filtered variable.
11 Replies
You could create a measure which only returns the prior year value if it is within the correct range, e.g.
Value for sales comp = VAR FacilityPriorYear = SELECTEDVALUE ( dimfacility[prior year sales] ) VAR CompPriorYear = SUM ( compdataset[prior year sales] ) VAR Result = IF ( CompPriorYear >= FacilityPriorYear - 10000 && CompPriorYear <= FacilityPriorYear + 10000, CompPriorYear ) RETURN ResultThanks for the input johnt75 but I think this only gets me to the same place I am now, where it is flagging the facilities from the compdataset that are within the range. How would this get implemented further to be able to use a slicer like mentioned above ("Show Sales Comps", "Show Qty Comps" or "Select All")?
You could create a disconnected table just for use in the slicer to show your 3 options, then create measures like
Value for sales comp = VAR SalesFacilityPriorYear = SELECTEDVALUE ( dimfacility[prior year sales] ) VAR QtyFacilityPriorYear = SELECTEDVALUE ( dimfacility[prior year qty] ) VAR SalesCompPriorYear = SUM ( compdataset[prior year sales] ) VAR SalesCompMatches = SalesCompPriorYear >= SalesFacilityPriorYear - 10000 && SalesCompPriorYear <= SalesFacilityPriorYear + 10000 VAR QtyCompPriorYear = SUM ( compdataset[prior year qty] ) VAR QtyCompMatches = QtyCompPriorYear >= QtyFacilityPriorYear - 100 && QtyCompPriorYear <= QtyFacilityPriorYear + 100 VAR ChosenOption = SELECTEDVALUE ( 'Disconnected slicer'[Option] ) VAR Result = SWITCH ( TRUE (), ChosenOption = "Select All" && SalesCompMatches && QtyCompMatches, SalesCompPriorYear, ChosenOption = "Sales Comp" && SalesCompMatches, SalesCompPriorYear, ChosenOption = "Qty Comp" && QtyCompMatches, SalesCompPriorYear ) RETURN ResultYou'd need to duplicate this in a measure for the quantity value.
This could be a good use case for the new DAX user defined functions if you're allowed to use preview features in production.
Thank you for the input. Unfortunately, this solution does not work because once we aggregate it and not look at each individual CompData Facility, the comparison is no longer at an individual level and the Prior Year Sales and Qty are now aggregated and there are no 'comps'
Hi ryan_b_fiting,
Please check the PBIX file; I've made some changes and got the following output.
Thank you
