RLS DAX to provide dynamic BETWEEN where a user can have multiple rows

[mark_endicott]

Hi, 

I'm trying to wrap my head around a complex RLS rule, where we need to filter the a column of a table to all the values between a start and an end point in the RLS table, however the complication is, a user can have multiple rows in the RLS table, and therefore multiple start and end values. 

The RLS table is in the strutcture below:

username	territory_code	time_grain	start_tgd_key	end_tgd_key
Test@test.com	AU	Week	11200249	11299901
Test@test.com	AU	Period	12200249	12299901
Test@test.com	AU	Quarter	13200249	13299901
Test@test.com	IE	Week	41200249	41299901
Test@test.com	IE	Period	42200249	42299901
Test@test.com	IE	Quarter	43200249	43299901
Test@test.com	NZ	Week	91200849	91299901
Test@test.com	NZ	Period	92200849	92299901
Test@test.com	NZ	Quarter	93200849	93299901
Test@test.com	UK	Week	111199801	111299901
Test@test.com	UK	Period	112199801	112299901
Test@test.com	UK	Quarter	113199801	113299901

And the table that the rule will be imposed upon like below:

Stat_week	territory_code	time_grain	tgd_key
199801	UK	Week	111199801
199801	UK	Period	112199801
199801	UK	Quarter	113199801
200249	AU	Week	11200249
200249	AU	Period	12200249
200249	AU	Quarter	13200249
200249	IE	Week	41200249
200249	IE	Period	42200249
200249	IE	Quarter	43200249
200849	NZ	Week	91200849
200849	NZ	Period	92200849
200849	NZ	Quarter	93200849

The rule needs to match the territory_code & time_grain values between tables, and then filter to all the values in tgd_key that are between the start_tgd_key and end_tgd_key. 

My starting point is the code below, which worked until we had to make it mulitple rows per user. 

VAR Start_filter =
    CALCULATETABLE (
        VALUES ( 'rls account_calendar_meta'[start_tgd_key] ),
        'rls account_calendar_meta'[username] = USERPRINCIPALNAME ()
    )

	
VAR End_filter =
    CALCULATETABLE (
        VALUES ( 'rls account_calendar_meta'[end_tgd_key] ),
        'rls account_calendar_meta'[username] = USERPRINCIPALNAME ()
    )
RETURN
    AND ( [tgd_key] >= Start_filter, [tgd_key] <= End_filter )

Grateful for any assistance that can be provided!!

Thanks,

Mark

[mark_endicott]

@OwenAuger - Thank you so much, both these rules work.

Having tested their impact through DAX Studio the version with TREATAS is the most optimal. More optimal in fact than a direct (m-to-m) join on all the possible values of [tgd_key] e.g. listing the [tgd_keys] in rls account_caledar_meta rather than using a start and end. 

I knew I needed to match on the [territory_code] and [time_grain] values, but I couldnt quite see how I was going to bring these values from the fact into my variables - it was actually more simple than I was making it. However I wouldnt have got to your NOT ISEMPTY solution at the end, so once again THANK YOU!!

[OwenAuger · Accepted solution]

Hi @mark_endicott 

Here are a couple of equivalent RLS filter expressons I would suggest:

(replace FactTable with the correct name)

VAR UPN = USERPRINCIPALNAME ( )
VAR CurrentTerritory =
    FactTable[territory_code]
VAR CurrentTimeGrain =
    FactTable[time_grain]
VAR CurrentTGDKey =
    FactTable[tgd_key]
RETURN
    CALCULATE (
        NOT ISEMPTY ( 'rls account_calendar_meta' ),
        'rls account_calendar_meta'[username] = UPN,
        'rls account_calendar_meta'[territory_code] = CurrentTerritory,
        'rls account_calendar_meta'[time_grain] = CurrentTimeGrain,
        CurrentTGDKey >= 'rls account_calendar_meta'[start_tgd_key],
        CurrentTGDKey <= 'rls account_calendar_meta'[end_tgd_key]
    )

VAR UPN = USERPRINCIPALNAME ( )
VAR CurrentTerritory = FactTable[territory_code]
VAR CurrentTimeGrain = FactTable[time_grain]
VAR CurrentTGDKey = FactTable[tgd_key]
RETURN
    CALCULATE (
        NOT ISEMPTY ( 'rls account_calendar_meta' ),
        TREATAS (
            { ( UPN, CurrentTerritory, CurrentTimeGrain ) },
            'rls account_calendar_meta'[username],
            'rls account_calendar_meta'[territory_code],
            'rls account_calendar_meta'[time_grain]
        ),
        CurrentTGDKey >= 'rls account_calendar_meta'[start_tgd_key],
        CurrentTGDKey <= 'rls account_calendar_meta'[end_tgd_key]
    )

The logic is to filter the 'rls account_calendar_meta' table based on the current fact table row, and return TRUE if 'rls account_calendar_meta' is nonempty.

Do these work for you?

Regards