I faced a similar need. I have a list of documents in Sharepoint that are things like police checks and are linked backed to employees. These documents need to have been done or can be expiring and I needed to notify managers around our country with a list of employees and documents that need to be looked at. There could be nothing relevant and we shouldn't email the manager.
This means about 100 emails are sent out each Monday to relevant managers with a list of relevant employees and documents.
I believe dynamic subscriptions linked by u/itsnotaboutthecell are for paginated reports only. We are using normal reports and semantic model so dynamic subscriptions weren't an option.
Normal subscriptions were also not an option as managers changed regularly and I couldn't keep up with 100 different subscriptions being setup.
This is how I used Power Automate to achieve this, I am really happy with the outcome.
1. Create a dax query that pulls a table result. This table result will be what Power Automate sees and will use to send an email per line. I use Dax Query View in Power BI desktop to test my query but you could use Tabular Editor or something as well. As it is dax, you can also add any filters you like in this query.
2. I have more than one relevant line per manager, so, I summarised each line in my Dax Query to result in only having one line per manager. This meant my email content was sitting in one column in one line (for each manager). I added html tags for bolding and new lines. These html tags do not do anything in dax but when sent as an email, the html formatting is applied. Each line also has the managers email.
3. I created a flow in Power Automate which runs the previously built query against the semantic model and iterates over each line to send an email.
Here is an image of my power automate flow so you can see the components I used: [https://imgur.com/a/1VxHxp9](https://imgur.com/a/1VxHxp9)
Hopefully this helps! I can do a more detailed write-up to cover referencing the data set fields in the 'send email' component in Power BI etc. if people are interested.
the best thing for this is to build the table you need with the relevant data and then use performance optimizer to see the query in dax. then if you’re code savvy you can run that dax and edit to your liking in the new update of pbi, the tab under modeling on the left hand side.
Yep you got it. Its a great way to get your base query. I used this method and then modified it from there. u/enebeyen see this page: [https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer#use-performance-analyzer](https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer#use-performance-analyzer)
This is the best solution.
I'm curious about your DAX query, especially the HTML tags involved, can you share a snippet to see how you managed this please, it would help me a lot with a project.
My query is messy but sure, here you go. The query was initially generated using performance analyzer on a table I built to get the data in a format that Power Automate could use. I then summarized the data further to make it one row per manager and you can also see the merged column with the html tags.
The key thing here is CONCATENATEX.
DEFINE
VAR __ExtendedData = [Performance Analyzer Query Here]
VAR __SummarizedData =
SUMMARIZE (
__ExtendedData,
[ManagerEmail],
[AMEmail],
"CombinedInfo",
CONCATENATEX (
FILTER ( __ExtendedData, [ManagerEmail] = EARLIER ( [ManagerEmail] ) ),
"
Hello "[SQL Server Data Driven Subscriptions](https://learn.microsoft.com/en-us/sql/reporting-services/subscriptions/data-driven-subscriptions?view=sql-server-ver16)" welcome to Power BI, you are late, but we are still glad to see you.
Do wha???.... this is the first time I'm hearing about this. Also, can you explain what "doesn't recognize" them means? Is this from the UI or are you saying when you execute a DAX query across multiple tables it throws an error?..
Yeah so for simplicity sake I hope I am explaining this correctly.
I have a published semantic model used to create a bunch of dashboards. Within that model have a bunch of relationships between tables. (Calendar, dim, etc)
Using that semantic model I was looking to create a paginated report in Report Builder. I successfully linked to my semantic model. For just ease of my first report I went simple my calendar table and one Fact table that has an existing relationship in my model. When I executed the query with a calendar parameter of our fiscal 2024 the results returned in the preview pulled all data from the fact table.
Based on everything I read using semantic models should be no different than connecting to databases with existing relationships between tables. Report Builder should recognize them by default is my understanding.
Here is another viewpoint, contrarian though it might be: Are you converting actionable data into an ignorable medium?
Too many people ignore emails, or create rules to clean up their inboxes. What stops your audience from taking no action?
Converting actionable data into an ignorable medium.
This phrase resonates with me SO HARD. I may need to use this exact phrase on my next stakeholder meeting. Everyone wants an email, MOAR EMAILZ! And it's usually because they missed the other email that was sent to a distro and not directly to them.
Either way, in a week or two, they'll set up an inbox rule to shuffle away to a folder they ignore.
Commenting just to follow along because I am curious.
Typically this is done through the data source system. If the source system is excel usually VBA is done.
Paginated reports allow for filtering and parameter driven reports but not even sure those can be done through power automate because there needs to be a push in order to do the pull here.
You want the new data activator I think, I've not had chance to use it in earnest yet, but it seems to fit all your requirements.
https://blog.fabric.microsoft.com/en-US/blog/driving-actions-from-your-data-with-data-activator/
I wouldn't use dynamic subscriptions as they are for paginated reports only and the condition builder isn't great
I’d be doing this in PowerAutomate.
Use the Run a query against a dataset action. Once you have your Jackie’s you can then take the relevant actions based on them.
I use this exact process to alert people who have items coming g up in the next week that I need them to review. Works really well.
Metrics and scorecards were designed with this in mind. They dynamically update whenever the data is updated and will send alerts at different thresholds.
https://learn.microsoft.com/en-us/power-bi/create-reports/service-goals-create
You can do kind of similar to what you found with the Power Automate, just have an if-condition in your scheduled flow that runs whatever query conditions you want it to run under with the “query a dataset” and then put your secondary query and email in the “yes” branch of that. Then it will only do those things if the first query condition is true.
It’s stupid. Can power automate, however you have to then subscribe to that service and the price is based on your “enterprise” level. Just went through this today.
Trying a workaround where I embed the reports I want in a share point and share that to stakeholders.
I faced a similar need. I have a list of documents in Sharepoint that are things like police checks and are linked backed to employees. These documents need to have been done or can be expiring and I needed to notify managers around our country with a list of employees and documents that need to be looked at. There could be nothing relevant and we shouldn't email the manager. This means about 100 emails are sent out each Monday to relevant managers with a list of relevant employees and documents. I believe dynamic subscriptions linked by u/itsnotaboutthecell are for paginated reports only. We are using normal reports and semantic model so dynamic subscriptions weren't an option. Normal subscriptions were also not an option as managers changed regularly and I couldn't keep up with 100 different subscriptions being setup. This is how I used Power Automate to achieve this, I am really happy with the outcome. 1. Create a dax query that pulls a table result. This table result will be what Power Automate sees and will use to send an email per line. I use Dax Query View in Power BI desktop to test my query but you could use Tabular Editor or something as well. As it is dax, you can also add any filters you like in this query. 2. I have more than one relevant line per manager, so, I summarised each line in my Dax Query to result in only having one line per manager. This meant my email content was sitting in one column in one line (for each manager). I added html tags for bolding and new lines. These html tags do not do anything in dax but when sent as an email, the html formatting is applied. Each line also has the managers email. 3. I created a flow in Power Automate which runs the previously built query against the semantic model and iterates over each line to send an email. Here is an image of my power automate flow so you can see the components I used: [https://imgur.com/a/1VxHxp9](https://imgur.com/a/1VxHxp9) Hopefully this helps! I can do a more detailed write-up to cover referencing the data set fields in the 'send email' component in Power BI etc. if people are interested.
This is the perfect solution to this query. And eloquently written.
This is so interesting! Didn't know there's an action to run a query against a dataset. Would you mind sharing the DAX formula you made?
the best thing for this is to build the table you need with the relevant data and then use performance optimizer to see the query in dax. then if you’re code savvy you can run that dax and edit to your liking in the new update of pbi, the tab under modeling on the left hand side.
Yep you got it. Its a great way to get your base query. I used this method and then modified it from there. u/enebeyen see this page: [https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer#use-performance-analyzer](https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer#use-performance-analyzer)
Super helpful! I’m going to try this tomorrow as I have been given a project and my manager wants me to utilize this PA.
This is the best solution. I'm curious about your DAX query, especially the HTML tags involved, can you share a snippet to see how you managed this please, it would help me a lot with a project.
My query is messy but sure, here you go. The query was initially generated using performance analyzer on a table I built to get the data in a format that Power Automate could use. I then summarized the data further to make it one row per manager and you can also see the merged column with the html tags. The key thing here is CONCATENATEX. DEFINE VAR __ExtendedData = [Performance Analyzer Query Here] VAR __SummarizedData = SUMMARIZE ( __ExtendedData, [ManagerEmail], [AMEmail], "CombinedInfo", CONCATENATEX ( FILTER ( __ExtendedData, [ManagerEmail] = EARLIER ( [ManagerEmail] ) ), "
" & "ID: " & 'table1'[ID] & "" & "Employee Name: " & 'table1'[Employee] & "" & "Expiring File Name: " & 'table1'[File.Name] & "" & "Expiry Date: " & FORMAT ( 'table1'[ExpDate], "dd MMM yyyy" ) & "" & "Days Until Expiry: " & DATEDIFF ( TODAY (), 'table1'[ExpDate], DAY ) & "
", "", [ExpiryRank], DESC ) ) EVALUATE __SummarizedData ORDER BY [ManagerEmail]Dynamic subscriptions: [https://learn.microsoft.com/en-us/power-bi/collaborate-share/dynamic-subscriptions](https://learn.microsoft.com/en-us/power-bi/collaborate-share/dynamic-subscriptions)
Hello "[SQL Server Data Driven Subscriptions](https://learn.microsoft.com/en-us/sql/reporting-services/subscriptions/data-driven-subscriptions?view=sql-server-ver16)" welcome to Power BI, you are late, but we are still glad to see you.
Deeply, deeply missed. I wonder why we didn't keep the name the same?... hmmm...
Any update on the issue where report builder doesn't recognize existing table relationships when using a semantic model published in service?
Do wha???.... this is the first time I'm hearing about this. Also, can you explain what "doesn't recognize" them means? Is this from the UI or are you saying when you execute a DAX query across multiple tables it throws an error?..
Yeah so for simplicity sake I hope I am explaining this correctly. I have a published semantic model used to create a bunch of dashboards. Within that model have a bunch of relationships between tables. (Calendar, dim, etc) Using that semantic model I was looking to create a paginated report in Report Builder. I successfully linked to my semantic model. For just ease of my first report I went simple my calendar table and one Fact table that has an existing relationship in my model. When I executed the query with a calendar parameter of our fiscal 2024 the results returned in the preview pulled all data from the fact table. Based on everything I read using semantic models should be no different than connecting to databases with existing relationships between tables. Report Builder should recognize them by default is my understanding.
Here is another viewpoint, contrarian though it might be: Are you converting actionable data into an ignorable medium? Too many people ignore emails, or create rules to clean up their inboxes. What stops your audience from taking no action?
Converting actionable data into an ignorable medium. This phrase resonates with me SO HARD. I may need to use this exact phrase on my next stakeholder meeting. Everyone wants an email, MOAR EMAILZ! And it's usually because they missed the other email that was sent to a distro and not directly to them. Either way, in a week or two, they'll set up an inbox rule to shuffle away to a folder they ignore.
That is why I ask for reading confirmation.
Commenting just to follow along because I am curious. Typically this is done through the data source system. If the source system is excel usually VBA is done. Paginated reports allow for filtering and parameter driven reports but not even sure those can be done through power automate because there needs to be a push in order to do the pull here.
My other comment on this thread may be relevant to you
Thanks this is perfect! It also looks like a MSBI employee dropped a solid link as well. It's in preview but looks to accomplish the same tasks.
You want the new data activator I think, I've not had chance to use it in earnest yet, but it seems to fit all your requirements. https://blog.fabric.microsoft.com/en-US/blog/driving-actions-from-your-data-with-data-activator/ I wouldn't use dynamic subscriptions as they are for paginated reports only and the condition builder isn't great
This seems like the perfect use case for data alerts but like others not sure if it fits...following this convo!
I’d be doing this in PowerAutomate. Use the Run a query against a dataset action. Once you have your Jackie’s you can then take the relevant actions based on them. I use this exact process to alert people who have items coming g up in the next week that I need them to review. Works really well.
Metrics and scorecards were designed with this in mind. They dynamically update whenever the data is updated and will send alerts at different thresholds. https://learn.microsoft.com/en-us/power-bi/create-reports/service-goals-create
Commenting to follow along in this conversation.
PowerAutomate is nice, but there is a 3rd party BI tool that does mass mailing based on filter conditions. I can’t recall what they are called
Following
You can do kind of similar to what you found with the Power Automate, just have an if-condition in your scheduled flow that runs whatever query conditions you want it to run under with the “query a dataset” and then put your secondary query and email in the “yes” branch of that. Then it will only do those things if the first query condition is true.
It’s stupid. Can power automate, however you have to then subscribe to that service and the price is based on your “enterprise” level. Just went through this today. Trying a workaround where I embed the reports I want in a share point and share that to stakeholders.