Also, there doesn’t seem to be a way to programmatically set alerts for specific emails in a PBI table.
In the example below, there are 10 email addresses (in practice there are 1000s). Jack should receive an email alert as his % in less than 100%, whereas Susan shouldn’t receive an email alert as her % is above 100%.
We don’t want to hard code these email addresses in Power Automate. We want PBI or Power Automate to go through the table containing emails and scores, and then email that user, dependant on if the alert is breached or not.
Is there a work around to hard coding the email address in Power Automate, and instead using a dynamic table/list of email addresses in PBI or even an external source?
You create a card visual that shows the Variance measure on a report page. You need this to trigger the emails. It can even been hidden and only be accessible through a bookmark if there is no other use for it in the report.
You put the card visual on a dashboard so you can set up the alarms.
You set up row level security so that each user sees her individual Variance value. You can use your email list to make this happen programmatically.
You create a Microsoft 365 security group from the list of email addresses that you use as the receiver for the email alerts.
Now step 4. is where I’d be curious to see whether this results in individual emails based on the individual value or everyone receiving the same email at the same time which could be a security issue.
If it doesn’t work, then it should work if every user sets up her individual alert with her individual email address.
Hi @Floyd, did the response provided by @Martin help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
The 3rd point is the catch. Use RLS on the email adress column using the DAX function called USERNAME () function and then proceed further as directed.
Let us know if you got your solution via this method or, not so that, we can think of any alternatives (if any).
The issue I have is how to get Power BI to loop through the various iterations of the one report.
Using the table example above the process would be:
Open the report and filter for Jack - check the alert - if breached send an email to Jack@email.com only
Open the report and filter for Gill - check the alert - if breached send an email to Gill@email.com only
Open the report and filter for Gill - check the alert - if breached send an email to Gill@email.com only
Etc, etc, etc x 10,000 times
So I would need Power BI to:
a) open the master PBI report, which includes 1000s of user names
b) filter and LOOP through each user name to check if alert is breached
c) if alert is breached, automatically send the associated user an email alert (the email address would be a field in the Power BI dashboard)
d) if the alert is not breached, then do nothing
