Forum Discussion

dhruvip23's avatar
dhruvip23
Regular Visitor
2 years ago

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.

1 Reply

  • 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.