Power BI: Displaying Realtime information in Power BI Dashboard using SQL Server

[@wick_bourne]

I need your assistance.... I think you must have forgetten something to mention @08:45. Because when i execute the sql query, Sensor 1 displays SUM value instead of latest value of sql table's SENSOR 1.

[@virtual_school]

As per my setup. Sensor one is just count down, so it shows maximum value, while sensor two is set to show average value. See @8:28. Did you change them from sum to maximum, and average?

[@wick_bourne]

@@virtual_school This is one way but how to view the latest value in SQL table using SQL query or something?

[@virtual_school]

For that you need another timestamp column and based on that you will need an sq query such as "select top 1 yourcolumn from yourtable order by timestampcolumn desc", or similar other query.

[@wick_bourne]

@@virtual_school Yeah correct. For that matter, we can also create and go with id column as well.

[@MoonShine-bs6cl]

Hi Awesome, I have one request please could you make a video on connecting dataset through API to web / application and refreshing it. It would b a new thing to learn for me... Thanks in advance

[@virtual_school]

Sure, why not. I will be glad to. Could you pls help me understand. Which API. What kind of web/application? Thanks

[@MoonShine-bs6cl]

@@virtual_school I have only heard about API which connects datasets like json to power bi, you can use any web database online for experiment, as I just want to know how this API works nd makes the dataset refresh... Thank u

[@virtual_school]

Did you see this video? kzbin.info/www/bejne/eKSwdXdnfMuapdU In this tutorial we connected power bi to world bank database, using their API. Is this what you are looking for?

[@MoonShine-bs6cl]

@@virtual_school yes I have seen but i have not understood, how this API works and should me schedule a refresh on this link that every day i get fresh data updated, and In this video I don't want country seperate query and few categories query is connected, in power bi microsoft you have Refresh dataset option using API... It starts (https:API.powerbi )etc, how do i use this please can u make a demo end to end...

[@SHAUNVINDA]

i do not have the refresh page option, any idea why?

[@virtual_school]

Probably you selected Import instead of DirectQuery on windows where it asks for server name.

[@rajkumarrajan8059]

This is lovely!!!

[@virtual_school]

Thanks 😊

[@MoonShine-bs6cl]

Hi awesome explanation, please could you make a same video on how to refresh data from sql server by using power automate it would be very helpful. Thanks in advance

[@virtual_school]

Sure. I will do a tutorial on using power automate with bi.

[@virtual_school]

Is this what you were looking for? kzbin.info/www/bejne/r36WaI2Yar5-bcU

[@aghanawazan]

Thanks but It works with only desktop, what about after publishing the report. we need to connect with gateway and schedule to refresh the data.

[@virtual_school]

Power BI is going through major changes. They have removed realtime support on power bi service. It was expected, therefore I never used one of those in my tutorials. powerbi.microsoft.com/en-us/blog/announcing-the-retirement-of-real-time-streaming-in-power-bi/

[@telugupadyalu...6927]

nice ..very nice

[@khaledelsafy]

excellent

[@virtual_school]

Thank you sir.

[@alvarogonzales-xp9qw]

what information do you see?? can you have a example, in real time when i change manually one value in table on excel to comunicate on sql server. to visualite en power bi.

[@virtual_school]

Sorry I could not understand your question.

[@beskal80]

why my power bi don't have page refresh?

[@virtual_school]

Probably because you are not connected to a supported database. Even if you are connect to SQL and choose import instead of directquery as data connectivity mode, the referesh page option will not be available. Visit this page for list of directquery supported data sources. learn.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources

[@beskal80]

@@virtual_school thank you..now i can see it

[@mejdithabet6145]

@@beskal80 How did you resolve the pb plz ?

[@AiBusiness-io2sm]

When I try to connect to SQL Server in Power BI I get this error message: CANNOT CONNECT Details: “Microsoft SQL: A connection to the server was successfully established, but an error occurred during the login process (provider: SSL Provider, error: 0 - The certification chain was issued by an untrusted entity).”????

[@virtual_school]

There is an option asking for if you trust your server, check it if you do, and then it will not require certificate.

[@MoonShine-bs6cl]

Hi, it's great experience learning from you, please can you show me one example on the power bi dashboard where my source was Excel first and now the same dataset is changed to Sql database, how do i update the sql database for automatic refresh in power bi and also how automation can b done through the connected application in power bi. Thanks in advance, as this will help me.

[@virtual_school]

First your data source was Excel, but now it is SQL. So excel is now irrelevant. You are connected to SQL server database, which is already automated. Data is updated into SQL server database from some input method. So all you have to do is turn on the page refresh in Power BI (Data should be connected through Power Query). All my realtime examples including this one uses this method.

[@MoonShine-bs6cl]

@@virtual_school Ok so i can refer your real time examples of SQL database which you hv used and how do i automate it that when it's connected to SQL server it should get refresh automatically. Thank you so much😊

[@virtual_school]

1. Make sure it is connect to the SQL server using "DirectQuery" method. 2. Make sure that the "Page Refresh" in turned on in Power BI, and time is set according to your requirement All of this is covered in the tutorials.

[@DuhaMohammed-n7j]

Hello, I cant seem to find the refresh button on my power bi desktop app, I am sure my dad is connected through "DirectQuery" but i cant refresh the page. if you could quide me in this.@@virtual_school

[@kaylacharity1429]

Will this report show real time data in power Bi service?

[@nglife8710]

how to publish this real time reports on Power bi cloud services so that clients can also see real time data for per second?

[@virtual_school]

You will need a Power BI subscription for that.

[@nglife8710]

@@virtual_school I have power bi pro subscription as well. Do i have to set or enable fixed interval refresh for every page on power bi service as well or is there any way to get data automatically updated after some seconds?

[@virtual_school]

@@nglife8710 Sorry can't help you with that, we are not using pro or premium right now. Only Power BI Desktop.

[@felipeantoniomoraramos6905]

@@nglife8710 Have you solved this? I have the same question

[@nglife8710]

@@felipeantoniomoraramos6905 yes just go with power bi premium and direct query approach it will work

[@mejdithabet6145]

Hello ! i didn't find auto refresh

[@virtual_school]

Did you connect to the data through DirectQuery? and what type of data source you are using. It works only with specific databases such as SQL Server.

[@mejdithabet6145]

@@virtual_school Yes. Last one i've in page Format is Filter Cards

[@virtual_school]

If Page Refresh option is not there, that means either you are not connected to the compatible data source, or you did not choose the DirectQuery during connection. I cannot think of any other reason.

[@MoonShine-bs6cl]

Hi i have one query, please can you make a video on cumulative percentage where for eg i have A - 16% \B - 25%\ C - 35%\D = 0% /E = 56% /F = 60% Example it should add 16+25+35+0+56+60 as result, but after 0% its not adding cumulative pls help

[@virtual_school]

I am having difficulty understanding your question. Please share a sample file. or if the problem is in a formula/function, please share that.

[@MoonShine-bs6cl]

I got this thank u

[@srdouglas1026]

Parabéns, mostra obtendo informações de ações

[@virtual_school]

How can we help you?

[@ruwanc2003]

This is not realtime, one second refresh

[@virtual_school]

Yes. I hope you did your cost and benefit analysis if you want to go for "real" real time and avoiding just one second of latency?

[@ruwanc2003]

@@virtual_school yes, I need realtime, datastreaming, can you do that?

[@virtual_school]

I do not use most of the stuff required for realtime datastreaming, so cannot do that right now. For me, directquery is the perfect solution as I can produce near to realtime reports on large databases without paying anything, facing any limitations and building complex models.