ByPass RLS with Disconnected Table - COMPLEX ISSUE HELP

[ryan_b_fiting]

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.

1. I have 4 tables, dimfacility, factsales, dimdate, CompDataSet
   1. Dimfacility JOINS to factsales on Facilitykey
   2. Factsales JOINS to dimdate on saledate = date
   3. CompDataSet is a disconnected table (only joins to date table)
2. Dimfacility contains facilitykey, facilityname, Prior Year Sales, Prior Year Qty and some other fields that are not relevant to this.
3. Fact sales contains saledate, facilitykey, sale Amount, Quantity
4. Compdataset contains facilitykey, salesdate, prior_year_sales, prior_year_qty
5. 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

1. 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.
2. 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.
3. I would need to repeat the same logic as #6 and #7 but for quantity, +/-100.
4. 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

[v-saisrao-msft]

Hi @ryan_b_fiting,

Please check the attached pbix file hope this helps your issue.

Thank you.

[ryan_b_fiting]

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'

[v-saisrao-msft]

Hi @ryan_b_fiting,

Please check the PBIX file; I've made some changes and got the following output.

Thank you

[ryan_b_fiting]

It does not appear that anything has changed in this new file.  The results remain the same

If I choose Show Sales Comps it should be 2 facilities (if I am showing facilities) and total PriorYearSales of 310,000.

However, we are not showing it on a Comp Facility by Facility Bases, we need to filter out the facilities that are not true comps and then aggregate/average.  But when I remove the facilityKey from the table, the total becomes the sum of ALL 3 facilities which is wrong.

[johnt75]

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
    Result

[ryan_b_fiting]

Thanks 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")?  

[johnt75]

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
    Result

You'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. 

[ryan_b_fiting]

Unfortunately preview features will not be allowed for this when rolling out to production.  I also am not sure this solution works unless I am looking at it on an individual facility by facility basis from the comp data.  

Ultimately I need to be able to select my slicer (Sales Comp, Qty Comp All Comps) but then be able to see all the Facilities that are flagged by that slicer and get the averages/percentiles aggregated into one number (average) or 3 numbers (Percentiles).

So the more things I have tried, I am starting to think there is not a true solution for this becasue we would need some sort of dynamic column that would show the RLS Facility Prior Year Sales and Prior Year Qty in the CompDataSet and then create the flag as as a column to filter on.

[johnt75 · Accepted solution]

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.

[ryan_b_fiting]

@johnt75 this actually may end up working.  I have tested it out in a few scenarios and it is working as of now.  If I have any issues I may reply to this thread.  Thanks for your help!

[ryan_b_fiting]

Thanks for the quick response @johnt75 I am trying to follow how that would work because how with the disconnected filter table filter that SUMMARIZECOLUMNS variable table that is calculated on the fly?  Seems like I am going to get to the same result, no?