Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
@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!!
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