topic Split a string of values into Columns in DAX Commands and Tips

Split a string of values into Columns

Anonymous — Fri, 25 Nov 2022 15:22:16 GMT

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.

Re: Split a string of values into Columns

amitchandak — Fri, 25 Nov 2022 15:50:30 GMT

Anonymous , 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

Re: Split a string of values into Columns

tamerj1 — Fri, 25 Nov 2022 16:19:08 GMT

Hi Anonymous

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

Re: Split a string of values into Columns

Anonymous — Fri, 25 Nov 2022 16:58:01 GMT

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.

Re: Split a string of values into Columns

Anonymous — Fri, 25 Nov 2022 17:00:04 GMT

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.

Re: Split a string of values into Columns

tamerj1 — Fri, 25 Nov 2022 18:28:23 GMT

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

Re: Split a string of values into Columns

Anonymous — Fri, 25 Nov 2022 21:17:16 GMT

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.

Re: Split a string of values into Columns

tamerj1 — Sat, 26 Nov 2022 08:53:29 GMT

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.

Re: Split a string of values into Columns

tamerj1 — Sat, 26 Nov 2022 17:11:37 GMT

Here is the same solution

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

Re: Split a string of values into Columns

Anonymous — Mon, 28 Nov 2022 15:26:06 GMT

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