r/PowerBI • u/Jaapuchkeaa • 1d ago
Discussion Made fully functioning CRM inside Power BI with database write-back.
Enable HLS to view with audio, or disable this notification
AI Summary -
This CRM runs entirely inside a Power BI report:
→ Drag a deal across a Kanban board, and its stage is saved to SQL Server
→ Click "+ New deal," and a form opens over the page, like a real app
→ Convert a lead into an organization, contact, and deal in one click
→ Log calls, notes, and tasks, with overdue tasks flagged in red
The whole model runs on DirectQuery, so every KPI and chart reflects the change on the next click.
How it works:
• A custom Power BI visual I built in TypeScript (Kanban, forms, activity log)
• A small Python API that validates every write
• SQL Server with constraints and a full stage history
The data model follows Frappe CRM
It's a single-user proof of concept for now. For production use, we have to use Azure SQL, an HTTPS API, and Microsoft Entra sign-in so it runs in the Power BI Service.
My doubts now
1- Is it worth to make CRM /Management tools in Power BI with API functions for write-back, or better to use a proper full-stack application?
2- How can this be useful in Production env especially after Fabric apps? Is it better to use that instead of this?
3- Can this now be considered a Power Automate replacement???
24
u/seguleh25 2 1d ago
Why do it in PowerBI instead of a Fabric App on a SQL database?
3
u/Jaapuchkeaa 1d ago
thats what i asked, yes ,will replicate this in fabric apps also , a full fledged CRM
2
96
u/ItzChiips 1d ago
It's so funny seeing people handicap themselves by staying with PowerBi.
I understand this is a PBI sub, I used to be all in.
Times have changed, it's incredibly limiting
42
4
u/EagleOdd9696 1d ago
Wouldn’t using fabric do the same thing as web environment but keep all the existing pbi architecture? Seems like a great jn between that would be easy to implement jn an org
-2
u/ItzChiips 1d ago
Yeah, it would be better than what OP did at least.
I have fully abandoned all Microsoft products though, I am a little biased
1
u/EagleOdd9696 1d ago
Fair. Will be interesting if orgs go full no miscrosoft vs something like fabric apps. Will boil down to the costs imo. Is your web solution much cheaper than a fabric setup? As an org fabric would be pretty quick to setup and connect to their data
0
u/ItzChiips 1d ago
Out of the box, Microsoft is better.
If you have someone who can dev proprietary tools they will always be better. I use Streamlit for a large bulk of my analytics and tools. $0 direct cost if you exclude our Oracle DB which we would have to pay for regardless. There are minimal costs related to the pods Streamlit runs on, but its overall cost is basically free since it's open source.
We are spinning down multiple enterprise tools Q1/Q2 next year and replacing them with my replacements. You can build out a one stop shop that holds everything in one spot.
1
u/EagleOdd9696 23h ago
Very curious on the speed of this. We’ve used pbi semantic model import mode and connect to pbi mcp run queries and it’s quick and accurate. I know pbi smenxtic model does some compression so I think it would be faster than just a sql or api questions especially for complex queries. Again haven’t done the web app side just thinking of what advantage msft would have over open source right now on the technical side
1
u/ItzChiips 22h ago
Do you mean the speed of the web side user experience?
Wicked fast. We have some dashboards that have millions of rows of data loaded up.
0
u/EagleOdd9696 22h ago
Actuslly you can still connect the semantic model to streamlit via api so visual part can still be done
1
1
u/Different_Syrup_6944 21h ago
What happens when you leave the company? Is there documentation and processes in place so you can go on leave?
2
u/ItzChiips 21h ago
Yes.
We have many many skill files and context layers.
I can leave tomorrow and anyone who knows English can pick things up
1
u/Different_Syrup_6944 20h ago
That's awesome
I'm trying to set up skills and context layers on my side as well. I'm working in a large multinational, but they still seem to be quite behind the times despite their best efforts.
What type of company are you working at?
0
u/ItzChiips 20h ago
We are a logistics company. Without getting too specific we facilitate intentional shipping
2
u/RogueCheddar2099 1 18h ago
Genuine question. What PBI limitations does Streamlit not have? Are there any downsides to Streamlit? Which industry were you developing for?
2
u/ItzChiips 18h ago
I wish we develed just full web dev. Streamlit does has limitations, but the limitations are nothing compared to PowerBi.
We have data analysis tools, push and pull from DB, bespoke interactive tools, scheduling, data extraction, forecasting, you name it we have done it so far. Its an all in one tool, that you can shape to what is needed
I would say the dowsides are the few limitations of pixel level control that true web dev allows. Streamlit abstracts Java and html into Python. With current LLM, we would be better off without those guardrails
I am in the shipping logistics industry
4
u/Jaapuchkeaa 1d ago
Elaborate
28
u/ItzChiips 1d ago
This is like building a nuclear reactor in a shoebox lol.
A CRM tool belongs in a web environment.
PowerBi was great before LLMs got really good at python and web dev. You can now build something better than this and much less limiting outside of PBI much quicker and easier.
1
u/Jaapuchkeaa 1d ago
yeah but exploring options
14
u/Ok-Advertising5189 1d ago
Sure, go ahead and explore your options, but you’re forgetting why this tool was created in the first place. Choose the tool based on the nature of the problem, not the problem based on the tool.
You’ve put together a cool tech demo, but it has no practical value. That doesn’t mean it’s pointless—it just means it doesn’t make sense in Power BI.
1
8
u/Pixelplanet5 4 1d ago
not really worth it do have any of this in powerbi.
this is basically a regular web app build into a powerbi dashboard, just use the webapp on its own with a minimal webserver, put it behind a ENTRA id login and you are done and much more flexible.
7
u/SunnyFIREwave 12h ago
Honest question: why would you build this in Power BI at all?
Don't get me wrong, getting a Kanban board with write-back running inside a report is a cool hack. But look at what you need for production: Azure SQL, an HTTPS API, Entra sign-in, a custom visual you have to maintain yourself. At that point you've already built 80% of a web app, just squeezed into a reporting tool that was never meant for it.
With something like Claude Code it's pretty easy these days to build your own web portal with security baked in. Row-level security, fully automated. The backend scripts aren't hard: pull from your HR system and decide who sees what based on role and department. Host it in your own portal and build the pages yourself in Next.js or whatever's current. If you're reasonably handy, I honestly think you could have the whole thing running within a week.
And then you're not fighting DirectQuery limits, visual sandboxing or licensing per user.
So yeah, this is kind of how I see things going for Power BI over the next few years. It'll stay great for analytics, but for anything app-like, the bar for just building it yourself has dropped a lot.
-2
u/Jaapuchkeaa 11h ago
There are multiple things, first is user requirement, there are still companies out there who do not want a separate application for CRM (budget constraints and sh*t), so they want Power BI to act like CRM,
And CRM is just for demo purposes; it could be used for multiple things like approvals, Live info, chat systems, etc.
8
u/skyline79 2 21h ago
This is no different than the games being played in Power BI. This isn’t a serious proposition.
6
4
u/Initial_Strength3952 1d ago
That's hilarious. You could even make power BI in power BI with AI next and call it Power AI at this point haha
Crazy you can do insane things like this in front of people and they still somehow are mega coping and in denial claiming it doesn't work with their "super complex real world scenarios with messy data" like it's not literally just solved 200+ papers of cutting edge mathematics and building complex web apps from scratch.
2
4
u/Emergency_Recipe522 12h ago
Pretty impressive proof of concept. You've demonstrated that it works, but I'd separate what's technically possible from what's practical to maintain. For a production CRM, I'd lean towards a proper application for transactions and workflows, with Power BI handling reporting and analytics. My biggest questions would be around multiple users updating records, permissions, auditing and long-term maintenance. Those requirements would influence my architecture decision more than the interface itself.
3
u/Ok-Advertising5189 1d ago
1- Is it worth to make CRM /Management tools in Power BI with API functions for write-back, or better to use a proper full-stack application?
The answer is simple—Power BI will impose more limitations on you than a comparable solution on Azure. And the Azure solution will be cheaper. As for the downsides—you’ll have to handle everything else yourself, like security, authorization, authentication, etc.—but it’s cheaper. If you’re considering completely replacing a tool with a ready-made Power BI report that offers the same functionality, it means that tool was simply meant to be replaced by something else—whether it’s Power Apps, Power Pages, another low-code solution, or an saas product.
3- Can this now be considered a Power Automate replacement???
Lol, no—if anything, only in certain scenarios. Power Automate is still a background orchestrator; you can trigger it from many places, including via HTTP, and integrate it with whatever you want
Your solution is just a nice interactive overlay… what you’ve done isn’t much different from PowerApps (where you can do the same thing—just embed any front-end code and it’ll work).
So, in short—you’re turning Power BI into something it isn’t. IMO, that’s not a good business case. Technically cool, but business-wise—so-so.
2
2
u/not_mantiteo 1d ago
I’m super interested in the write back capabilities. I’ve had to use sharepoint lists which will set off a flow to update my dashboard. Obviously that’s clunky because it takes people out of the dashboard
2
2
u/DeepLogicNinja 4h ago
First…. impressive, consider this a prototype/mvp for a proper full stack app.
1- you already know the answer. “better to use a proper full-stack application?” Yes.
2- “How can this be useful in Production env especially after Fabric apps? Is it better to use that instead of this?”
For scale you need to separate reporting/application in your architecture.
Reporting queries are typically expensive and shouldn’t be ran against the same schema as the application. One analytics/reporting user running one report can slow down the entire application for all users IF you share the same schema.
Often, people try to use the same schema for
both. It can work but fails at scale. You can tell the team/developers’ strong point by just looking at the schema. Although it’s the same data, you need to store it in the right schema for the application or analytics. You can use views/materialized views to flip between the two
- database schema should be denormalized for analytics (OLAP) star, Snowflake, and or OBT schema optimized for reads, less joins, and highly indexed. Would hurt insert/updates but you don’t care because you’re optimizing this schema for select and aggregate functions for analytics.
- database schema should be normalized 3NF for application usage (OLTP). Schema is optimized for updates/inserts and light selects/joins for an application UI / record view.
Note: When the application needs a scorecard/dashboard aggregation summary type screen, of search…. you should use a report against the OLAP schema. When you have ALOT of data your app’s OLTP may start to get slower, and you can remove older data knowing you can still query the older data in the OLAP / Data Warehouse Schema.
3- “Can this now be considered a Power Automate replacement???” No.
It’s a common data pipeline from an application (CRM) to a data warehouse for reporting. You can use views like I mentioned above, and just refresh those materialized views on a schedule. It’s called the prototyping data warehouse use case.
You are currently sourcing the CRM schema/data…. Imagine also augmenting your reporting with an accounting system and other applications/external data for reporting.
The denormalized view on the OLTP tables-approach can still work, but you’ll need to reconcile data across multiple systems. This is the point where introducing an ETL tool and a data governance tool makes sense.
1
1
u/imgroot9 1d ago
but you cannot modify existing data in rows, just add new ones, correct? at least this was the biggest limitation when I tested it a while ago.
1
1
1
u/dupontping 18h ago
I would LOVE to see you run this under capacity. You will see how quickly this isn’t going to work.
I don’t understand why people do things that aren’t meant to be done.
I can put on a wetsuit, fill the bathtub and hold my breath, but that doesn’t make me a diver.
1
u/Mr_Mozart 10h ago
Fun, cool and interesting! But, please don't do this... Every platform has its advantages.
1
u/TheEmpyreanWanderer 7h ago
This is pretty awesome. Any chance you have this on GitHub or would share the source code?
1
u/Acceptable-Sense4601 3h ago
people do everything except make apps in a proper language. this is like making a video game in excel. learn something new for fucks sake.
1
u/Jaapuchkeaa 2h ago
understand the purpose , the purpose is to make writeback in PowerBI, CRM is just a example here , and for writeback py and ts has been used,
18
u/qooooob 22h ago
PowerBI and Fabric, believe it or not, are tools for analytical workflows, not operational workflows. Just like you shouldn't use powerapps code apps to replace PowerBI, you shouldn't make a CRM with PowerBI or fabric apps. Dynamics365 Sales is literally a model driven powerapp ran on dataverse with the same alm etc. It is not worth the hassle and the latest trend with fabric apps is really confusing.