Forum Discussion
Filtering Data using MID and SWITCH functions to apply RLS in PowerBI using DAX editor
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.