Sending emails from Power BI via Power Automate

[musafasih]

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:

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!

[jennratten]

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