If you use Power BI for data driven decision making you probably had the need to ask a client/supplier/user for updates on specific key metrics from your report.
You might be thinking now “Oh, but you can add comments in reports and dashboards…”, but what if the person you want to contact doesn’t have access to this specific report? What if you want to send an email asking for updates?
That’s where DAX hyperlinks come into play!
We will go through how you can easily add a hyperlink to a table that will automatically open a pre-defined email with all your main information (with a little caveat, the email won’t be automatically formatted, sorry folks ☹).
Just click send!
Key words: Power BI, DAX Hyperlinks, mailto
1) As an example, I used a dataset I found online with some info on Leeds Schools.
First, I built my table with the information I wanted to see.
Now, what we want to do is, instead of having the email column in my table, I want it to be a link that I’m able to click, and that will open a new window with a personalised email for me.
2) We are going to add a new column (I named it Mailto) where we will define what we want in our email.
You might now already that the mailto function opens a new email window based on the selection you’ve made on the table. But what, maybe, you didn’t know yet is that you can add users on CC, an email subject, and also define the body of the email using data from Power BI.
This is what the measure for my new Mailto column looks like:
Mailto = "mailto:" & 'Leeds Schools Info'[Email] << TO
& "?cc=" & "manager@datapears.com;" & "marapereira@datapears.com" << CC
& "&subject=" & "Placed students update for " & 'Leeds Schools Info'[School] & " for the year of " & FORMAT(TODAY(), "YYYY") << SUBJECT
& "&body=" & "Hi " & 'Leeds Schools Info'[Headteacher] & ", We are now in the process of checking the data related to the placed students in " & 'Leeds Schools Info'[School] & ". Could you please provide this data at your earliest convenience?
Thanks, Data Pears Team" << EMAIL BODY
As you can see, I used the data from my table in Power BI to add the school name in the Subject and the Headteacher in the body!
3) After you’ve created the column for your personalised email, change the data category of the column to Web URL:
4) We will now go back to our table, and on the Visualizations Pane select format. Open the Values section and make the option URL Icon active:
5) Now you just need to add your new column to the table. You will see that this new column will show up with an email icon:
Lastly, we just need to test our new functionality:
Notes:
a) As I mentioned before, the email won’t be formatted. If you want it to look nicely formatted just edit it a bit.
b) When testing the email hyperlink on Power BI Desktop you will get a very ugly warning. Just ignore it and click Allow.
As today is Power BI 5th anniversary…
Happy birthday Power BI!!