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