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

Sending emails from Power BI via Power Automate

HI all,

 

My  Power BI report consists of three tables : Jobmaster- containing Project details such as start date, finish date and project manager details; Transactions- containing transactions related to the projects in Jobmaster, including invoices and costs, and Calendar- the date table. The measures used relevant to this topic are as follows:

  1. ‘Date of last transaction’: showing last date of transaction for every project
  2. ‘Days since last transaction’: showing the number of days that have lapsed since last transaction for every project
  3. ‘Invoices less costs’- showing the difference between invoiced amount and the cost charged for eahc project

Since I am not able to post my dummy data here, I will pos a snapshot of my data table:

 

musafasih_0-1659657755831.png

 

musafasih_1-1659657792175.png

 

 

I want to use Power Automate for two situations:
a) For jobs where ‘invoices less costs’ measure displays negative for any job, I’d like to send an email to the project manager (preferably a single email for a project manager listing his jobs and the negative amount) notifying him to issue more invoices on those projects.

b) For projects where ‘days since last transaction’ has exceeded 30 days, I’d like to send an email to the project manager notifying that there has been no transaction for that job (again preferably one table per manager listing all jobs under him). Also, the email ought to be sent after every 10 days after the 30 days threshold.

 

Thanks heaps!

 

1 REPLY 1
jennratten
Super User
Super User

Hello - I think this would be best handled with a Power Automate visual (or two).  I recommend creating new table visuals in your PBI report that are organized in the way that you'd like the data to be distributed.  

Example, for item a, create a new table visual that has these columns in the following order: project manager, email, project, invoice less costs and filter the table for invoice less costs.  Either place a filter on invoice less costs to show only those amounts that are negative OR create a new measure for something like PM Notification Amount - the objective of either would be to return only records with negative amounts.  Then add a Power Automate visual to the PBI report and drop those fields into the field well.  You can then go to Power Automate and configure your flow, which will allow you to bring in the fields that you configured and you can use a For Each statement to loop the rows in the table, where you can choose to email the PM (send an email for each PM containing the details.

Here are some resources that should help you:

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-automate-visual?tabs=powerbi-deskt... 

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-automate-power-bi-report-export 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors