cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
_Spock Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Super User II
Super User II

Re: Filter file path to only show file name

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 🙂

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Super User II
Super User II

Re: Filter file path to only show file name

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 🙂

Proud to be a Datanaut!

View solution in original post

_Spock Frequent Visitor
Frequent Visitor

Re: Filter file path to only show file name

2. option worked perfekt.

Thanks a lot.

 

Will keep an eye on performance.

 

/Spock

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors