Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Split a string of values into Columns

Hi All, I am using Embeded power BI, in the Username() i am getting list of values "A,B,C,D". When i am reading this it is considering as a string not as a list of values. Anyone come accross this s...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Tamerj,

    i have created a measure same as mentioned.  I assume that it returns the table but somehow i am not able to see any thing. Do i need to create any table prior to this?

    RLS_Key =
    VAR String = "P1,P2,P3" -- place your sting here
    VAR Items =SUBSTITUTE ( String, ",", "|" )
    VAR Length =COALESCE ( PATHLENGTH ( Items ), 1 )
    VAR T1 =GENERATESERIES ( 1, Length, 1 )
    RETURN
    SELECTCOLUMNS (T1,"Project",PATHITEM(Items,[Value]))
     
    Could you please clarify.

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Tamerj1, Thanks for responding. I am using Embeding Power BI. In that we are using RLS. In general secenarios, RLS works based on Username(), Userprincipalname() where it will return either user name or user email id. With embeded power BI RLS scenario, we can get any value in the username() as part of the requirement on which value we should filter the data. As part of RLS, i am getting a string with list of values where user can see the report only with list of values corresponding data. Here string contains "Program1,Program2,Program3.." like so on. I should able to read this string and filter the data for those program related data. As i get the list of values in the form of string i need to store this as a list or column and then only can filter. 

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Anonymous 

I don't fully understand but you may create a table using the following DAX

List =
VAR String = "P1,P2,P3" -- place your sting here
VAR Items =
SUBSTITUTE ( String, ",", "|" )
VAR Length =
COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
GENERATESERIES ( 1, Length, 1 )
RETURN
SELECTCOLUMNS ( T1, "Project", PATHITEM ( Items, [Value] ) )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Tamerj,

    i have created a measure same as mentioned.  I assume that it returns the table but somehow i am not able to see any thing. Do i need to create any table prior to this?

    RLS_Key =
    VAR String = "P1,P2,P3" -- place your sting here
    VAR Items =SUBSTITUTE ( String, ",", "|" )
    VAR Length =COALESCE ( PATHLENGTH ( Items ), 1 )
    VAR T1 =GENERATESERIES ( 1, Length, 1 )
    RETURN
    SELECTCOLUMNS (T1,"Project",PATHITEM(Items,[Value]))
     
    Could you please clarify.

     

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Anonymous 
      Again, I'm sorry but I really don't understand what is this string or what are you trying to achieve. However, the proposed DAX is for a calculated table as per your request not a for measure.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Thanks for your reply. As suggested instead of using as measure used the same in the Manager role. It is working as expected.