Power BI Report Server Data and SQL Server
Hi All and d_gosbell, My company purchased SQL Server Enterprise Edition 2019, with which I got the Power BI Report Server (Jan 2022 Version). Since our client had a strict on premises requirement. I develop Power BI Dashboards on the Power BI Desktop (For Report Server Jan 2022 Version). I then save it on the Power BI Report Server Web Portal. I have 2 main questions; 1) I develop a Power BI Dashboard which displays project progress for multiple departments such as Engineering, Construction, Procurement, etc. I get multiple data sources (Manual and Live) for this report. Eg: I get an Excel/ PDF/ Word File sent by Email every week, I have to load Excel manual data from SharePoint, I have already linked to some Oracle/ SQL Databases for live data, etc. Currently, what I do is, for example, My KPI is Project Plan Vs Actual Data. I get data for this table on a weekly basis. I have stored this data in an Excel Table which I linked to the Power BI Dashboard. When I get next week's data, I will overwrite on the current data in the Excel table. The issue with this is every week I'm overwriting the Past Week's data. So I'm not able to retain past data, and will always only have the current week's data. My requirement is to store all weeks data in one place. I cannot only have the recent week's data. I need to have a filter which can show all the week's data. Question, how and where am I supposed to store every week's data for different visuals? Can I store it in Excel (Where every week I add new rows in the table instead of overwriting the past data), or do I store all this data in a database such as SQL? 2) My 2nd Question is this. I'm aware that when I develop Dashboards and save it in the Power BI Report Server. My data gets stored in the SQL Server (SQL Server Management Studio) - the one we installed along with PBIRS. --What kind of data get saved in the SQL Server? How do I access that? --Based on Question 1 - Can I use this SQL Server as a database where I store all my week's data for all my Power BI Visuals? How would that work exactly? Kindly assist on the above. Thank you in advance for your support!
Multiple connection error when refreshing dataset
Hello, This morning all my data sets failed to refresh. This also happaned when i tried to manually trigger the refresh. Below the error message I received via email: I have checked the Fourth_DimEmployees table but on the SQL server all looks good. Also, when I manually tried to refresh it, I got the error from another table. When I try to see the raw data in PowerQuery, it times out while refreshing the page: Finally, I did not make any changes to the dataset in the last 2 weeks. So very strange to get this error. If anyone has any idea on how to fix this or if it is an issue with Microsoft, please let me know
Get the average of a column value based on total count of values in ID column
Hi, In a table i have column called "Object" which runs multiple times in a day and that runtime is captured in "StartTime" & "EndTime" columns, and the difference between the start and end time is captured in the "Duration" Column. Each object will be having multiple runid's which is captured in "RunID" column which is a unique value. For eg: Object AAA has two runid's called "111,222" and Object BBB has one runid called "333". Now i would like to get the average of each duration by count of that particular runid which is captured in "New Duration" column. For eg: The count of 111 runid is '10'. so for all those durations with runid 111 should be divided by the 10. The count of 222 runid is '6'. so for all those durations with runid 222 should be divided by the 6. The count of 333 runid is '8'. so for all those durations with runid 333 should be divided by the 8. Sample screenshot for above query: So since my Duration is "10" and total count of runid (111) is 10. So 10/10=1 that is my newduration.Similarly for all durations with runid's 111 should be divided bt 10. How to achieve this using DAX query?? I am connecting to this table in powerbi via SQL direct query mode. Thanks.
