This has helped me tremendously. I am working on the next step of this. Say, automating it for many regions...and if for some region the threshold is crossed, it sends and email to the regional manager of that region only. Similarly for others....
This training is such a miracle! I really appreciate your video, you are my hero! The only subject I wish you covered is about formating the "Date" which is a common problem.
Great video I want to do the same project but I want to make a scheduled daily refresh of the report and I want it to send an email daily after each refresh. With the report content included in the email
This was super useful and helpful. Is there a way though where I can format the grand total row to appear at the bottom of the other values leading to it?
Thank you for the video. One question - instead of a value in a card visual, can the alert be generated based on a value in a table visual? Meaning if some of the values in a particular column have breached the threshold value
Thank You! sir, This video helped me a lot to accomplished what I wanted to do in Power BI and Power Automate. Please make a video on formatting the table cell with different colors based on the values which each cell satisfied.
@belenlopezalduncin9278 not yet, I'd completely forgot about it. I'll add it to my list though but I'm not sure how long it will take I'm afraid to say.
Great job, sir! If I wanted to send more than one visual, must I need to add an AND() condition to the logic? I was thinking about sending the columns chart and the table
Thank you, it helped a lot! I will try to work on it. I have a question: I have a table that contains a lot of data, but only a few of them are relevant to send to my manager. So have to take out all "null" values by filtering in that filter area in "filters on this page". When I copy the query for the table, it also applies this filter? I am afraid to send a huge table with irrelevant values. Thanks! Awesome video :)
Thank you Jason, excellent video. Can I ask you for help please, Im not able to create the "total" row so that it doesnt show in blank.. thanks in advance
hi Jason, can I ask have you encountered this error before? The variable 'Number of Draft per month' of type 'Array' cannot be initialized or updated with value of type 'Integer'. The variable 'Number of Draft per month' only supports values of types 'Array'.
Thanks Jason, great run through. How would you recommend formatting dates as they currently come through in ISO 8601 Formats. I tried using the formatDateTime function at the select state as below but no luck. formatDateTime(item()?['Source[Date]'], 'MMM ddd yyyy'). Any help is welcome
Thanks for the video! but can i understand if this flow will automatically get the updated data from the scheduled refresh that we set in power bi service, and the data will be updated in the table embed?
Hi, The flow will use whatever data is in the power bi report when it runs. So if your power bi report has a scheduled refresh each morning and you run the flow after the scheduled refresh it will create a table with that data embedded in it. Hope that answers your question. Cheers Jason
Hi Prasuna, To get a specific value you can use the approach explained here: ua-cam.com/video/tbNvNk11sXM/v-deo.html The same principle applies to getting specific value from a table. In this video use action to extract the whole dataset so get every table value as in ua-cam.com/video/tbNvNk11sXM/v-deo.html this part of the video. Hope that helps, Cheers, Jason
Hello, nice video as usual , do you know guys how to get rid of the blank values working with the same method were should i add another step ? Thank you! :D
Hi !!!! Tihis is an excellent tutorial !!! Seriously, saved me a lot of time. I have a question though... The grand total goes at the first row (just under the column headers) of your HTML table example... Is there a way to move it at the bottom ? I know the HTML code to format the last row... but right now my grand total aren't the last one. Thx in advance.
This is great and appreciate the stepping through the process. I am using Fiscal Week instead of Month and am having trouble sorting by the fiscal week number. Any tips to do that? Thanks
Nice, but how can I filter by a value when a value of table will be X for example?. I have a lot of rows, but I need to know how can I send a email when a value of row in that table equals X value.
Hey @Jason , I was wondering if it is possible to do something similar to this but instead of it being if a specific value of KPI changes it sends to the people we choose, I wanted to drill it down more. So if that value was changed but only because of a specific country then only people from that country would get that email. For example, I have a target of 5 apples per day and only one of the countries goes below that target, then i want only a specific person of that country to get an alert email...
How can I applicate a condition in each value of a table if I extract that in a query of power bi?. You used a card, but I need filter a table . Help me please
Plz help , i can't get values other than month in select step, they all show as null , followed exactly same as shown but can't get it right. plz plz help
Hi, without seeing more of what you are doing I can't really help. You can ping me a pm on LinkedIn if you want to exchange emails. www.linkedin.com/in/jasondavidson
@@jasondavidsondata Hey , I tried and I was able to get it finally, I really appreciate your quick response. I will surely connect on LinkedIn. Thanks again 🙏
how did you get ('Get_YTD_Cost_vs_Budget_Pct_Value')?['body/firstTableRows'][0]?['Total_Cost_YTD_vs_Budget_YTD_Diff_]'] Could you please explain how you got this expression?
Hi, It looks like that measures do not export, only text content can be exported to csv. Is it possible to export the table with text and measures both, PLEASE? I see the values in "Input" but "Output" shows "null" therefore nothing saved in csv file. // DAX Query DEFINE VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('_____PrerequisitesPK'[Deployment Date])), NOT('_____PrerequisitesPK'[Deployment Date] IN {BLANK()}) ) VAR __DS0Core = SUMMARIZECOLUMNS( ROLLUPADDISSUBTOTAL( ROLLUPGROUP('_____PrerequisitesPK'[Installation Time GMT], '_____PrerequisitesPK'[Batches]), "IsGrandTotalRowTotal" ), __DS0FilterTable, "PK_Dist_Count_Exclude_Offline__14Days", 'Measures PK'[PK Dist.Count Exclude Offline >14Days], "PK_Dist_Count_Offline_Exclude_Offline__14Days", 'Measures PK'[PK Dist.Count Offline Exclude Offline >14Days], "PK_FT_Pending_NOT_on_WiFi_Exclude__4Days_Offline", 'Measures PK'[PK FT Pending NOT on WiFi Exclude >4Days Offline], "PK_FT_Done_Exlcude_Offline__14Days", 'Measures PK'[PK FT Done Exlcude Offline >14Days], "PK___FT_Done_Exclude_Offline__14Days", 'Measures PK'[PK % FT Done Exclude Offline >14Days], "PK_INS_Done_Exlcude_Offline__14Days", 'Measures PK'[PK INS Done Exlcude Offline >14Days], "PK___INS_Done_Exclude_Offline__14Days", 'Measures PK'[PK % INS Done Exclude Offline >14Days], "PK___INS_Pending_Exclude_Offline__14Days", 'Measures PK'[PK % INS Pending Exclude Offline >14Days] ) VAR __DS0PrimaryWindowed = TOPN( 502, __DS0Core, [IsGrandTotalRowTotal], 0, '_____PrerequisitesPK'[Batches], 1, '_____PrerequisitesPK'[Installation Time GMT], 1 )
EVALUATE __DS0PrimaryWindowed
ORDER BY [IsGrandTotalRowTotal] DESC, '_____PrerequisitesPK'[Batches], '_____PrerequisitesPK'[Installation Time GMT]
You are a life savior. Thank you for this video!
Great job Jason. Solid explanations without too much technical talk. Thank you
Thanks Andrew, glad you found it useful. 👍
This has helped me tremendously. I am working on the next step of this. Say, automating it for many regions...and if for some region the threshold is crossed, it sends and email to the regional manager of that region only. Similarly for others....
Hi bro, I need some help with the automating data alerts for regions , could u revert back ?
Really like the idea of using performance analyzer to get query code.
This training is such a miracle! I really appreciate your video, you are my hero!
The only subject I wish you covered is about formating the "Date" which is a common problem.
The explanation provided was excellent and included a thorough, step-by-step walkthrough 👍👍
Hi Hamza, thanks for your comment and I'm happy you found the video useful 😀
Great video
I want to do the same project but I want to make a scheduled daily refresh of the report and I want it to send an email daily after each refresh. With the report content included in the email
Wow! Game changer! My mind is spinning thinking of all the applications of this… thanks!
Glad it was helpful! I was the same when I found out about this.
thank you for the detailed explanation
This was super useful and helpful. Is there a way though where I can format the grand total row to appear at the bottom of the other values leading to it?
This is great!
What if I have a column with email addresses and I want to send the email to the associated emails with the applied dax filter?
THIS is exactly what I am looking to do as well
Thank you for the video. One question - instead of a value in a card visual, can the alert be generated based on a value in a table visual? Meaning if some of the values in a particular column have breached the threshold value
Thank You! sir, This video helped me a lot to accomplished what I wanted to do in Power BI and Power Automate. Please make a video on formatting the table cell with different colors based on the values which each cell satisfied.
Hi Vishal, thanks for your comments 😊. That's a good idea for a video too.
@@jasondavidsondata Hi, have you done a video on this in the end? I've been looking everywhere. thanks for the video it was very helpful
@belenlopezalduncin9278 not yet, I'd completely forgot about it. I'll add it to my list though but I'm not sure how long it will take I'm afraid to say.
Helpful video. Thanks a ton.
Thanks for the video! Very helpful!
That's great, glad you ground it helpful and thanks for the comment
Mate I hope this video is not taken down by tomorrow!
thanks for your sharing.
How to delete the grand total row in json table?
Thanks for the great video. Would this method apply to embedding a Matrix as well?
Great video but it's mind boggling how easy this was to do in SSRS and how painful this is in PBI. How is there no tool to solve for this yet?
its soo good .my work is done but its not show percentage value .for my side its show as number .what to do
It might need you to wrap a format statement around the number in the power be query. I think I cover it in the part of the video.
Thanks for that one , very helpful!
Cheers Dave, thanks for the comment 😀
Great job, sir! If I wanted to send more than one visual, must I need to add an AND() condition to the logic? I was thinking about sending the columns chart and the table
Thank you, it helped a lot! I will try to work on it.
I have a question:
I have a table that contains a lot of data, but only a few of them are relevant to send to my manager. So have to take out all "null" values by filtering in that filter area in "filters on this page".
When I copy the query for the table, it also applies this filter? I am afraid to send a huge table with irrelevant values.
Thanks! Awesome video :)
Hi Bruno, thanks for the comment 👍😀
When you copy the dax code for the table visual it should include the necessary code to filter the null values.
Hi All,
This was a very helpful...
How can I display the total row at the bottom instead of the top?
I have been searching this video for such a long time...🤟
This is such a priceless video and I have literally lost the count I have referred this...💌🤟
@Jason, a quick question, i have a dashboard with several filters, how can i apply different filters and send mails like this video..is it feasible?
Thank you Jason, excellent video. Can I ask you for help please, Im not able to create the "total" row so that it doesnt show in blank.. thanks in advance
When i update the data in report next day will the power automate flow will email that updated data or this static data only everytime we run the flow
It refreshes with dataset, the DAX will be applied over the dataset.
Awesome! thank you so much, this will be very useful
hi Jason, can I ask have you encountered this error before?
The variable 'Number of Draft per month' of type 'Array' cannot be initialized or updated with value of type 'Integer'. The variable 'Number of Draft per month' only supports values of types 'Array'.
This is a great video! Thank you
Thanks Jason, great run through. How would you recommend formatting dates as they currently come through in ISO 8601 Formats. I tried using the formatDateTime function at the select state as below but no luck. formatDateTime(item()?['Source[Date]'], 'MMM ddd yyyy'). Any help is welcome
Thanks for the video! but can i understand if this flow will automatically get the updated data from the scheduled refresh that we set in power bi service, and the data will be updated in the table embed?
Hi, The flow will use whatever data is in the power bi report when it runs. So if your power bi report has a scheduled refresh each morning and you run the flow after the scheduled refresh it will create a table with that data embedded in it. Hope that answers your question. Cheers Jason
@@jasondavidsondatareally appreciate the answer Jason, this video is so helpful!
Also in select we are getting Column Headers, how to get column values?
Hi Prasuna,
To get a specific value you can use the approach explained here: ua-cam.com/video/tbNvNk11sXM/v-deo.html
The same principle applies to getting specific value from a table. In this video use action to extract the whole dataset so get every table value as in ua-cam.com/video/tbNvNk11sXM/v-deo.html this part of the video.
Hope that helps,
Cheers,
Jason
Hello, nice video as usual , do you know guys how to get rid of the blank values working with the same method were should i add another step ?
Thank you! :D
Make more videos on power automate, i love your content😊😊😊
Hi !!!!
Tihis is an excellent tutorial !!! Seriously, saved me a lot of time. I have a question though... The grand total goes at the first row (just under the column headers) of your HTML table example... Is there a way to move it at the bottom ? I know the HTML code to format the last row... but right now my grand total aren't the last one. Thx in advance.
I found it !!!!! Just need to change the DESC to ASC into the Dax Code at section ORDER BY at the bottom : [IsGrandTotalRowTotal ASC,
This is great and appreciate the stepping through the process. I am using Fiscal Week instead of Month and am having trouble sorting by the fiscal week number. Any tips to do that? Thanks
Hi! Im having trouble sorting also the tables, have you had any luck? tips to share? thanks
Fantastic!
Cheers, glad you found it useful. 👍 😀
When I run my flow, I could see only header in the email. and values are all null. Could you suggest what could be wrong here.
Thanks,
Nice, but how can I filter by a value when a value of table will be X for example?. I have a lot of rows, but I need to know how can I send a email when a value of row in that table equals X value.
Very nice!
Hey @Jason , I was wondering if it is possible to do something similar to this but instead of it being if a specific value of KPI changes it sends to the people we choose, I wanted to drill it down more. So if that value was changed but only because of a specific country then only people from that country would get that email. For example, I have a target of 5 apples per day and only one of the countries goes below that target, then i want only a specific person of that country to get an alert email...
Hi Daniel, sounds like it should be doable. I'll look at creating a video if I figure it out.
@@jasondavidsondata did you create a video like this? I need exactly the same. Thank you!
How can I applicate a condition in each value of a table if I extract that in a query of power bi?. You used a card, but I need filter a table . Help me please
Is there a way to add the report as xlsx attachment instead of link
Plz help , i can't get values other than month in select step, they all show as null , followed exactly same as shown but can't get it right. plz plz help
Hi, without seeing more of what you are doing I can't really help. You can ping me a pm on LinkedIn if you want to exchange emails. www.linkedin.com/in/jasondavidson
@@jasondavidsondata Hey , I tried and I was able to get it finally, I really appreciate your quick response. I will surely connect on LinkedIn. Thanks again 🙏
how did you get ('Get_YTD_Cost_vs_Budget_Pct_Value')?['body/firstTableRows'][0]?['Total_Cost_YTD_vs_Budget_YTD_Diff_]'] Could you please explain how you got this expression?
Seriously need help to understand this output stuff at 5:54 that is being used as target value. Any help is much appreciated.
How to do This in power bi desktop?
Like that i want to sent multiple tables in mail in a single report
This is awesome
How to i Contact you sir
Hi, It looks like that measures do not export, only text content can be exported to csv. Is it possible to export the table with text and measures both, PLEASE? I see the values in "Input" but "Output" shows "null" therefore nothing saved in csv file.
// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('_____PrerequisitesPK'[Deployment Date])),
NOT('_____PrerequisitesPK'[Deployment Date] IN {BLANK()})
)
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP('_____PrerequisitesPK'[Installation Time GMT], '_____PrerequisitesPK'[Batches]), "IsGrandTotalRowTotal"
),
__DS0FilterTable,
"PK_Dist_Count_Exclude_Offline__14Days", 'Measures PK'[PK Dist.Count Exclude Offline >14Days],
"PK_Dist_Count_Offline_Exclude_Offline__14Days", 'Measures PK'[PK Dist.Count Offline Exclude Offline >14Days],
"PK_FT_Pending_NOT_on_WiFi_Exclude__4Days_Offline", 'Measures PK'[PK FT Pending NOT on WiFi Exclude >4Days Offline],
"PK_FT_Done_Exlcude_Offline__14Days", 'Measures PK'[PK FT Done Exlcude Offline >14Days],
"PK___FT_Done_Exclude_Offline__14Days", 'Measures PK'[PK % FT Done Exclude Offline >14Days],
"PK_INS_Done_Exlcude_Offline__14Days", 'Measures PK'[PK INS Done Exlcude Offline >14Days],
"PK___INS_Done_Exclude_Offline__14Days", 'Measures PK'[PK % INS Done Exclude Offline >14Days],
"PK___INS_Pending_Exclude_Offline__14Days", 'Measures PK'[PK % INS Pending Exclude Offline >14Days]
)
VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'_____PrerequisitesPK'[Batches],
1,
'_____PrerequisitesPK'[Installation Time GMT],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC,
'_____PrerequisitesPK'[Batches],
'_____PrerequisitesPK'[Installation Time GMT]
You found the solution, plz share as I am also looking for the same