Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to create a report that gives me a chart of open ticket counts for each day.
For example, on 7/1/2020, there are 200 tickets open. The data on 7/1/2020 will stay that way even if there are tickets closed on 7/2/2020. This will be a line chart seeing how many active tickets were present on each particular day. I have thought of using a count with Date tables on rows that are between Ticket Open date and Ticket Close date, but I do not have Ticket Close date column, and any tickets that are closed are erased from the data.
What I thought of is using Power Apps to manually append the data instead. Every day, I will go into Page 1 of the report, it will give me a count of the ticket that is still active, then, I will go to Page 2 and enter the number into the Power App visual, and that number will be appended to a new table, Table X. My chart will then be referencing to Table X. In short, someone will have to enter the number every day.
However, I do not have experience with Power Apps. Does anyone know how to implement what I need?
Thanks!
Daren
Solved! Go to Solution.
@darentengmfs - this really isn't a Power BI question. Power BI will read data from many sources, as you know.
You are taking it to the next level with using Power Apps to record your data, vs someone doing it in Excel or with another app.
As @DataZoe there are ways to add data without Power Apps, like using the default Forms in SharePoint lists.
I love using Power Apps though to do what you want to do, but if you have no experience with it, then I cannot recommend the App in a Day course highly enough! It is free, and here.
Once you get that done, you just create your Power BI report and set the refresh schedule. You can also trigger a refresh from the Power Apps app via Power Automate, but you are limited to 8 refreshes a day with a Power BI Pro license. Power Automate refreshes and Scheduled Refreshes add up. Once you've done 8 refreshes, no more will happen until the next day. If you have Premium capacity, it is 48 per day.
If you get bold, then the Direct Query route @DataZoe mentioned can be refreshed unlimited times, but this has drawbacks:
Once you get your head around what you want to do and have done the App in A Day course, the Power Apps community can jump in and assist with building the app.
My personal advise - go slow. Use SharePoint Lists as your data source for the app. Don't jump in too deep and get frustrated with gateway configs, proper indexes and whatnot in a SQL Table. Power Apps requirements for SQL Server are higher than Power BI. Both can read any SQL table. But to write, Power Apps needs a unique index field that is hopefully generated by the server on the fly, or you get into creating GUIDs or your own incrementing logic.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @darentengmfs ,
I would consider the following idea.
Create a data flow in PowerApps, which saves the ticket status in the CDS in the morning. The data record should not be overwritten via an ID / key (date). You should then be able to load the CDS Entity into Power BI.
This inspired the idea.
@mwegener - doesn't CDS require a premium connector for Power Apps licensing?
I know CDS is a much preferred data source than SharePoint Lists, but the latter is included iwth Office 365 Power Apps licensing in most cases. CDS becomes $40/mo I believe.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans ,
Licensing is not my specialty and is no fun, but here is my current level of knowledge.
There are different types of license.
- Power Apps & Power Automate use rights included with Office 365 licenses
- Power Apps & Power Automate use rights included with Dynamics 365 licenses
- Power Apps per App Plan
- Power Apps per User Plan
- Power Automate per User Plan
- Power Automate per Flow Plan
Except for the Office Plan, all should include Common Data Services. But please check that again.
@darentengmfs Which license do you use?
I believe that is correct @mwegener - which was why I recommended SharePoint LIsts. I fully agree CDS is a better long term solution, especially for projects that will have thousands of records, but it does come with the added expense of $7 to $40 per month per user depending on what other plan is selected.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
Thanks for the thorough explanation.
I am opting to use Sharepoint Forms to update my data.
However, I read it here that I will have problems with the data gateway if I connect to both SQL and Sharepoint.
I don't know if it has been resolved, so could you share some input to me if you have had experience with connecting Power BI to both SQL server and Sharepoint in the same report?
Thanks!
Daren
Hi @darentengmfs That is from 2016. MS upgraded the service at least in 2017 to use both on-prem and cloud data in the same report. I use SharePoint with on-prem SQL Server every day in Power BI.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@darentengmfs - this really isn't a Power BI question. Power BI will read data from many sources, as you know.
You are taking it to the next level with using Power Apps to record your data, vs someone doing it in Excel or with another app.
As @DataZoe there are ways to add data without Power Apps, like using the default Forms in SharePoint lists.
I love using Power Apps though to do what you want to do, but if you have no experience with it, then I cannot recommend the App in a Day course highly enough! It is free, and here.
Once you get that done, you just create your Power BI report and set the refresh schedule. You can also trigger a refresh from the Power Apps app via Power Automate, but you are limited to 8 refreshes a day with a Power BI Pro license. Power Automate refreshes and Scheduled Refreshes add up. Once you've done 8 refreshes, no more will happen until the next day. If you have Premium capacity, it is 48 per day.
If you get bold, then the Direct Query route @DataZoe mentioned can be refreshed unlimited times, but this has drawbacks:
Once you get your head around what you want to do and have done the App in A Day course, the Power Apps community can jump in and assist with building the app.
My personal advise - go slow. Use SharePoint Lists as your data source for the app. Don't jump in too deep and get frustrated with gateway configs, proper indexes and whatnot in a SQL Table. Power Apps requirements for SQL Server are higher than Power BI. Both can read any SQL table. But to write, Power Apps needs a unique index field that is hopefully generated by the server on the fly, or you get into creating GUIDs or your own incrementing logic.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@darentengmfs There are some licensing considerations for embedding Power Apps into Power BI. But, it is doable, and there is a short video here https://youtu.be/us0hPFwQ4Zs (Guy in the Cube ~10 mins) that shows one way.
Another options would be:
1. to have it as a sharepoint list as the data source of the table in Power BI, so you could enter the new row for each day there, then Power BI picks it up on a scheduled refresh or manual refresh after it's been added.
2. create a table in Azure SQL Database and connect it to Power BI Desktop as direct query. You can update the table directly, or set up a Power Automate (Previously known as Flow) to update it from another source on edit (excel file, sharepoint list, whatever).
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |