Split a string of values into Columns

[Surekha]

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 situation, where i need to put this into a table as list of values and use that column in the RLS then my data gets filtered. Really facing very challenging. OR Is there any alternative ways to read the access token can retrieve the list of values and store those values? really very tricky. Your suggestion and guidence much appreciated.

[tamerj1]

Here is the same solution

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Embedded-RLS/m-p/2930629#M96402

[tamerj1]

Hi @Surekha 

please provide one clear example of what are you trying to achieve. 

[Surekha]

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]

@Surekha 

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] ) )

 

[Surekha · Accepted solution]

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]

@Surekha 
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.

[Surekha]

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

[amitchandak]

@Surekha , Option one remove double quotes and split commas into new rows

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

also check this option

Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c

[Surekha]

HI Amit, Thanks for your response. Is there a way i can insert a measure value in a new table. After that i can do the transformation the way you shared the details.