Checking file existance in Sharepoint with URL based on DAX?

[Anonymous]

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).







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.

[Anonymous · Accepted solution]

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



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

[v-luwang-msft]

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