Forum Discussion
ByPass RLS with Disconnected Table - COMPLEX ISSUE HELP
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.
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.
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.
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.
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?
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!