topic Dax trim function in DAX Commands and Tips

Dax trim function

simona0808 — Wed, 03 May 2023 07:56:10 GMT

Hello everyone,

I am working on a project and I would like to ask for some help.

As you can see in the image the left column(injured body parts) is coming from a checklist and is stored in a list separated by ;#. Is there a chance to trim that and add every injured body part related to the id column separately? I just need it on separate lines so I can make a relationship with another table that stores the single body parts

Re: Dax trim function

ValtteriN — Wed, 03 May 2023 08:15:19 GMT

Hi,

I am not 100% I follow but I assume you want to separate the list using ;# as delimiter and have the results show on different rows?

If you have access to powerquery I recommend doign this there like this:

Right-click and select "split column by delimiter" -> Select custom and enter ;#

Select created column and unpivot:

In dax you can do with e.g. using substitute function and some tricks. Here is an example blog of this by entreprisedna: Power BI Split Column By Delimiters In DAX (enterprisedna.co)

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Dax trim function

simona0808 — Wed, 03 May 2023 08:18:34 GMT

Thank you very much for your answer! Thats what I want. I tried your approach but it says that it cant be applied in direct query mode.

Re: Dax trim function

ValtteriN — Wed, 03 May 2023 08:29:43 GMT

In Directquery mode I am pretty sure this won't work and you will have to use dax option. If possible try changing your conenction to import/composite model then the original soltuion I provide should work.

Re: Dax trim function

simona0808 — Wed, 03 May 2023 08:32:15 GMT

Unfortunately, I am not able to change it due to customer requirements. Do you know how this can be done using Dax?

Re: Dax trim function

ValtteriN — Wed, 03 May 2023 09:08:49 GMT

It can be done with dax for example like described in the blog I linked

Re: Dax trim function

simona0808 — Wed, 03 May 2023 10:14:18 GMT

I tried to implement the steps from the tutorial however I encountered an error that pathlength is not a function and can be used in a direct query mode( again the same problem)

Re: Dax trim function

Anonymous — Wed, 10 May 2023 08:33:05 GMT

Hi simona0808 ,

What type of data source are you worked on? If you mean SQL or other type of Datasources which support T-SQL queries, I’d like to suggest you turn on the 'advanced option' -> 'SQL statemen' options in data connector to use custom T-SQL query to handle these conversions.

Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng

Re: Dax trim function

simona0808 — Thu, 11 May 2023 07:44:35 GMT

I believe I need to use a SQL query to achieve my goal. Currently, I have a column in my database that contains multiple values per row, as it's derived from a checklist. However, for my Power BI report, I need to use a single value as a page-level filter. To do this, I would like to split the values in this column into separate rows so that each row contains only one value. Can you assist me in creating a SQL query to accomplish this task?