Filtering Data using MID and SWITCH functions to apply RLS in PowerBI using DAX editor

dhruvip23 — Wed, 24 Jul 2024 05:26:38 GMT

Hey folks,

I have school data to filter using ENumbers where numbers are staring with 'E' followed by any 3 digits(eg. E1234, E2134, E3124, E4321). If the first number followed by 'E' is starting with 1 it should return data for School 'A', with 2, data for School'B', and so on. I want to apply RLS so that only 'school A' can see the data related to that school and so is the others.

I have an exaple which I've applied in SSMS using DAX, working fine while retrieving the data but giving me an error while applying it with DAX editor to apply RLS.

EVALUATE
SELECTCOLUMNS(SchoolInfo
, "SchoolENumber", SchoolInfo[CEM ENumber]
, "SchoolCode" , MID(SchoolInfo[CEM ENumber],2,1)
, "SchoolLabel" , SWITCH( MID(SchoolInfo[CEM ENumber],2,1)
, "1" , "A"
, "2" , "B"
, "3" , "C"
, "4" , "D"
, "Unknown")
)

If anyone can help me with this that would be great. Thanks in advance.

Re: Filtering Data using MID and SWITCH functions to apply RLS in PowerBI using DAX editor

FreemanZ — Wed, 24 Jul 2024 05:19:43 GMT

Hi dhruvip23 ，

not sure if i really get your point, but you might need to add the SchoolLabel column as calculated column and use that column for RLS.

topic Filtering Data using MID and SWITCH functions to apply RLS in PowerBI using DAX editor in DAX Commands and Tips

Filtering Data using MID and SWITCH functions to apply RLS in PowerBI using DAX editor

Re: Filtering Data using MID and SWITCH functions to apply RLS in PowerBI using DAX editor