Forum Discussion

ryan_b_fiting's avatar
ryan_b_fiting
Post Patron
11 months ago
Solved

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 relat...
Need Help
Show and Tell
Tips and Tricks
  • johnt75's avatar
    johnt75
    10 months ago

    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's avatar
ryan_b_fiting
Post Patron
11 months ago

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's avatar
johnt75
Super User
11 months ago

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.