Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
_Spock
New Member

Filter file path to only show file name

Hello

 

First of i am nwe to power BI, tried to search, but could not find a solution.

 

I am running a direct query on a SQL.

In my database there is a file path colum. But in my slicer i Power BI i want to only show the file name. Is it possible ?

 

The file path looks like this:

\\ARNE01\project\023\File453\Filename.doc

\\ARNE44\project\554\File324\Filename02.doc

In my slicer i woul like to only show:

Filename.doc

Filemane02.doc

 

Basiclly want to filter all left of the last \ 

 

Hope someone can help, thanks

 

/Spock

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

if you weren't using Direct Query I would suggest solving this in M, but I think it's unlikely to work in this case

In DAX you can create measure like this

Filename = MID(Table1[FileName],30,50)

it assumes that the folder path always has 30 characters
This is more flexible, but less performant

Filename =
TRIM (
    RIGHT (
        SUBSTITUTE ( Table1[Filepath], "\", REPT ( " ", LEN ( Table1[Filepath] ) ) ),
        LEN ( Table1[Filepath] )
    )
)


most performant may be just adding the filename in your SQL source though

 
EDIT the second code will not work if the filenames have spaces in them



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

2 REPLIES 2
Stachu
Community Champion
Community Champion

if you weren't using Direct Query I would suggest solving this in M, but I think it's unlikely to work in this case

In DAX you can create measure like this

Filename = MID(Table1[FileName],30,50)

it assumes that the folder path always has 30 characters
This is more flexible, but less performant

Filename =
TRIM (
    RIGHT (
        SUBSTITUTE ( Table1[Filepath], "\", REPT ( " ", LEN ( Table1[Filepath] ) ) ),
        LEN ( Table1[Filepath] )
    )
)


most performant may be just adding the filename in your SQL source though

 
EDIT the second code will not work if the filenames have spaces in them



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

2. option worked perfekt.

Thanks a lot.

 

Will keep an eye on performance.

 

/Spock

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors