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
Anonymous
Not applicable

Checking file existance in Sharepoint with URL based on DAX?

Can anyone help me with the following?

I'm building a report for an exported sales archive to consult old (Excel) data and lots of associated PDF files.
The PDF files were automatically created and are stored in a SharePoint library. The URL is not part of the Excel data.
There is however a consistance in the name convention of the PDF files, which contains order and invoice number.
With that, I created a new column which generates the necessary URL's based on this data, which looks like this:

URL invoice = "https://tenant.sharepoint.com/sites/archive/documents/pdf/"&data[Year]&"/"&data[Ordernumber]&"-invoice-"&data[Invoicenumber]&".pdf"


I switched data category to 'Web URL' and I created a matrix which contains ordernumer and assosiated URL's with an URL icon (see image below).

2022-06-15_13-59-27.jpg





So far so good. Now comes the challenge.
Power BI creates a URL for every instance, whether a file exists or not. For example, not every order has a credit note.
Is there a possibility to show the URL icon, ONLY if the specific file EXISTS in the SharePoint library?

Any help would be appreciated.





1 ACCEPTED SOLUTION
Anonymous
Not applicable

I created a workaround which gives me the required result. I created an extra column, which checks if an invoice number exists in the data:

Invoice_available = if(data[invoice]<>"","Yes","No")

Next I added an if-statement on the URL measure:

URL invoice = IF(data[Invoice_available]="Yes",
"https://tenant.sharepoint.com/sites/archive/documents/pdf/"&data[Year]&"/"&data[Ordernumber]&"-invoice-"&data[Invoicenumber]&".pdf"",
"")

This shows a URL icon if a match is found, otherwise it remains empty

2022-06-21_10-19-51.jpg

Not 100%, but it gives the required result. 🙂

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

I created a workaround which gives me the required result. I created an extra column, which checks if an invoice number exists in the data:

Invoice_available = if(data[invoice]<>"","Yes","No")

Next I added an if-statement on the URL measure:

URL invoice = IF(data[Invoice_available]="Yes",
"https://tenant.sharepoint.com/sites/archive/documents/pdf/"&data[Year]&"/"&data[Ordernumber]&"-invoice-"&data[Invoicenumber]&".pdf"",
"")

This shows a URL icon if a match is found, otherwise it remains empty

2022-06-21_10-19-51.jpg

Not 100%, but it gives the required result. 🙂
v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

Based on my test, it's not supported to display image in Matrix visual value part. As matrix value will be aggregated and return just "values" rather than image.

 

Best Regards

Lucien

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
Top Kudoed Authors