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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors