Set alerts on table visualization

[robbert-bi]

Hi all. I have a challenge regarding alerts in Power BI Service. I have a table visualization which looks like below (I recreated it in Excel, but it looks the same in Power BI). Every record represents a unique Project ID with a project manager, budget hours, actual hours and resulting excess expressed as a percentage. There are more than thousand projects and the table keeps being filled with new projects automatically. 

What I want to obtain is that every time a project has excess hours greater than 0% (hence, the projects with the red cells), an alert is being sent, both to the project manager which belongs to the project, but also to other selected persons in the organization (such manager of the project manager). As alerts in dashboards are limited to gauges, KPI's and cards I have tried to make cards per unique project but with potentially thousands of projects and new projects coming in every day this is not maintainable. 

Does anyone know how I can fix this? Thank you in advance for your help!

[AnkitKukreja · Accepted solution]

Hi! @robbert-bi 

You have to look out of Power BI for this. You can use Python scripts or Power Automate (PA).

For PA you can use dax like:

AlertFlag = IF([Excess Percentage] > 0, 1, 0)

then you can create a table viz and necessary use other necessary cols, use it in PA viz and create a flow to send out emails.

[v-menakakota]

Hi @robbert-bi ,

I just wanted to kindly follow up to see if you had a chance to review the previous response provided by community members. I hope it was helpful. If yes, please Accept the answer so that it will be helpful to others to find it quickly.

Thank you.

[rohit1991 · Accepted solution]

Solution:

1. Add an Alert Flag:

   • Create a calculated column in Power BI:
     AlertFlag = IF([ExcessPercentage] > 0, 1, 0)
     
     

2. Power Automate Integration:

   • In Power BI Service, trigger a Power Automate flow for flagged rows.
   • Use "When a data-driven alert is triggered" in Power Automate to send emails to project managers and stakeholders.

3. Test & Activate:

   • Test the flow and activate it for real-time alerts.
     
     
     

For further reference please visit ---> https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-flow-integration

[AnkitKukreja · Accepted solution]

Hi! @robbert-bi 

You have to look out of Power BI for this. You can use Python scripts or Power Automate (PA).

For PA you can use dax like:

AlertFlag = IF([Excess Percentage] > 0, 1, 0)

then you can create a table viz and necessary use other necessary cols, use it in PA viz and create a flow to send out emails.

[robbert-bi]

Hi @AnkitKukreja. Thank you very much for your reply! I will look into this. However, isn't there a solution in Power BI itself? Or with Data Activator?

[AnkitKukreja]

Hi! @robbert-bi 

Within Power BI as you are aware you have to use KPI in Dashboard.

[robbert-bi]

Hi @AnkitKukreja . Yes I know that I can only use a KPI, gauge or card and that is exactly the problem, because I have a table visualization. So I can't work with a KPI, gauge or a card.

But if I understand you correctly, it is not possible in Power BI and I will have to use Power Automate?

[AnkitKukreja]

Yes Power Automate or Python scripts might help! I haven't tried python myself though, but there will be a way to achieve this with python.

[robbert-bi]

Thanks @AnkitKukreja. So below you will find the steps that I took now based on your explanation. Can you advise how to fix this?

- I created an "AlertFlag" column in the table visualization and then I pinned this table to a "dashboard".

- I created 4 cards, the first one filtered on Project ID equal to "1", the next one filtered on "2", etc. and then I pinned these cards to the same "dashboard". See the first screenshot below.

- In the dashboard I can only set alerts on the cards as you also mentioned. So, I created alerts on the four cards. 

- Then I go to Power Automate, I create a flow from the template "Send an e-mail to any audience when a Power BI data alert is triggered". This flow only lets me choose from the alerts which I created in the "dashboard". 

- So what I did here is creating cards, pinning them to a dashboard, creating Power BI alerts and then using the specific "Alert Id" in Power Automate. However, this is not what I need, because I have more than 1000 projects in the table and new projects keep coming in.

How do I create a Power Automate flow which looks at the entire AlertFlag column in the table that I created? And then for each Project ID which has AlertFlag = 1, send an email to the Project Manager? 

[AnkitKukreja]

Hi! @robbert-bi 

I haven't tried this, but I will definitely try it. If you are using the Fabric Capacity, please try the set alert option on the table itself.
Create alerts in Power BI and use them in Fabric Activator - Microsoft Fabric | Microsoft Learn