Generic Commentary Writeback in Power BI using Fabric User Data Functions
Business users often need to explain why a number changed, not just view the number. These explanations usually end up in Excel, emails, Teams messages, or PowerPoint and become disconnected from the actual Power BI report context. I built a generic commentary writeback framework using Microsoft Fabric User Data Functions. The idea is simple: Selected business context + metric + period = one unique comment context. A user can select a period and metric, choose a row in a matrix or use report slicers, enter a comment, and save it directly from Power BI. For example: 🎯Target achieved at 104.5% — performance is above plan due to stronger volume and improved customer mix. What makes it generic? Instead of hardcoding Cost Center, Profit Center, Customer, Material, and every other dimension into the writeback function, Power BI dynamically creates a grain key from the current report context. This means the same framework can support commentary at different levels, such as Cost Center + Profit Center or Cost Center + Profit Center + Material, without redesigning the writeback function for every combination. Versioning and audit Comments are stored in a versioned backend table. Every insert, update, or delete creates a new version. A latest-comment view returns only the current active comment to Power BI, while the complete history remains available for audit and traceability. I used Databricks as the backend for this demo, but the same design can be extended to Fabric Warehouse, Azure SQL, or another suitable SQL-based storage layer. Why I built this The goal was to bring business commentary closer to the data itself and make explanations contextual, reusable, and auditable. This pattern can be useful for finance variance commentary, forecast assumptions, sales performance notes, planning annotations, and other enterprise reporting scenarios. The most interesting part for me was using Fabric User Data Functions with Power BI Translytical Task Flows to turn a report from a read-only analytical experience into an actionable workflow. Would love to hear how others are approaching commentary and writeback scenarios in Power BI and Microsoft Fabric. slindsay Praful_Potphode tharunkumarRTK Natarajan_MCustom AI integration
You can enable custom-tailored AI assistance directly on your report or kick off AI-powered custom workflows. Example, you can integrate Azure OpenAI into your report to help plan your next proposal while factoring in context from the report. You simply select the influencer and click the ‘Generate AI Suggestion’ button, which runs a Fabric User data function that instantly provides an Azure OpenAI response based on a fully customizable prompt. Here is the Python code for the user data function that powers this scenario: import fabric.functions as fn import logging import openai udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB",alias="Translytical") @udf.function() def AISuggestion(sqlDB: fn.FabricSqlConnection, company: str) -> str : logging.info('Python UDF trigger function processed a request.') # Establish a connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() #Get offer status for the company SQL_read_Command = "SELECT * FROM [dbo].[CompanyStatus] WHERE Company = ?" cursor.execute(SQL_read_Command, company) record = cursor.fetchone() customer_name = record[0] last_comment = record[10] #Submit prompt to Azure OpenAI and get an AI suggestion prompt = "Respond with a short plan that is under 240 characters: I work at Contoso Outdoors, and we collaborate with influencers by offering them offers for custom designed bikes. Pretend we want to collab with the following influencer: " + customer_name +" from company: " + company + ". Here's a comment about their latest feedback " + last_comment + "." deployment = "gpt-4o" openai_client = openai.AzureOpenAI( api_key='<API Key here>', api_version = "2025-01-01-preview", azure_endpoint = "https://sico-oai-eus2.openai.azure.com/openai/deployments/gpt-4o/chat/completions?api-version=2025-01-01-preview" ) response = openai_client.chat.completions.create( model=deployment, messages=[ {"role": "user", "content": prompt} ] ) result = response.choices[0].message.content #Check if there is an exisiting AI suggestion for the company SQL_read_Command = "SELECT * FROM [dbo].[AISuggestions] WHERE Company = ?" cursor.execute(SQL_read_Command, company) if cursor.fetchone(): #if there is an existing AI suggestion record for the company, update just the AI suggestion SQL_update_command = "UPDATE [dbo].[AISuggestions] SET [AI_suggestion] = ? WHERE [Company] = ?;" Existing_Suggestion = (result, company) cursor.execute(SQL_update_command, Existing_Suggestion) else: #if there is NOT an existing AI suggestion record for the company, add a new record SQL_insert_command = "INSERT INTO [dbo].[AISuggestions](Name, Company, AI_suggestion) VALUES(?, ?, ?);" New_Suggestion = (customer_name, company, result) cursor.execute(SQL_insert_command, New_Suggestion) # Commit the transaction connection.commit() # Close the connection cursor.close() connection.close() return f"Generated Azure OpenAI suggestion for collaboration ideas with " + customer_name + " from " + company + "." Feel free to use this code as inspiration for your own custom AI integration scenarios!Handling Multi-Row Inputs for Translytical Task Flows
This post offers a practical guide to passing multiple rows of data from a Power BI report to a User-Defined Function (UDF) using translytical task flows. The solution leverages the TOJSON() DAX function to overcome the limitation of scalar-only inputs from slicers. Translytical Task Flows At their core translytical task flows allow buttons within your Power BI reports to trigger a UDF. Within the button definition you map a button, list, or text slicer; a data field; or measures from the report to the input parameters of the UDF. However, a key limitation is that when using a slicer, you can only pass a single (scalar) value. If a user selects multiple items, the button becomes inactive, preventing the UDF from being called. But with a measures, you are able to pass tabular data a string, with the help of the seldomly used DAX function TOJSON(). Sudoku This example uses a Sudoku game built with Power BI to demonstrate the solution. The game state is stored in a Fabric SQL database, and UDFs are used to manage game actions. The game UI consists of several components: Game Selection: A slicer to select a game template. Game Grid: A button slicer representing the 81 cells of the Sudoku board. Each button uses a DAX-generated SVG to display numbers and pencil marks. Number Selector: A button slicer for selecting the number to enter (1-9). Pen/Pencil Buttons: Buttons that trigger UDFs to enter numbers or pencil marks into the selected cell(s). The architecture is as follow: The Pen and Pencil buttons are mapped to the position from the grid button slicer and the value from the value selector button slicer. This works perfectly for a single cell selection. Generally in Sudoku, you want to mark two or more cells simultaneously when entering pencil marks. However when we select more than one cell, the Pen and Pencil buttons becomes inactive: Using TOJSON() to Pass Tabular Data To enable multi-cell input, we need a method to generate a single string that encapsulates information about multiple selected positions. For those familiar with REST APIs, calling the UDF in this context is analogous to a POST request, where the JSON format is the preferred payload. Fortunately, DAX provides a perfect solution: the built-in TOJSON() function. This function generates a JSON string from a given table. We can create a DAX measure that generates a JSON string containing all the selected grid positions. Selected Positions = IF( ISFILTERED( 'Grid Position'[Position] ), TOJSON( VALUES( 'Grid Position'[Position] ) ) ) This provides the following output: We can update the mapping from the position provided by the grid button slicer, to the DAX measure: Handling the JSON in the UDF The Python code in the UDF can then parse this string back into a list of positions to be processed. import fabric.functions as fn import json from datetime import datetime udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="Game") @udf.function() def toggle_pencil_mark(sqlDB: fn.FabricSqlConnection, gameId: int, position: str, pencilValue: int) -> str: """ Toggle a pencil mark in a Sudoku cell Args: gameId: The game ID position: JSON string with cell positions pencilValue: Pencil mark value (1-9) Returns: JSON string with operation result """ # Parse positions from JSON positions_data = json.loads(position) positions = [pos[0] for pos in positions_data["data"]] ## Open connection and SQL database and write values to cells conn = sqlDB.connect() cursor = conn.cursor() processed_positions = [] last_action_type = None last_is_active = False for pos in positions: # Skip cells with values cursor.execute("SELECT CurrentValue FROM GameState WHERE GameId = ? AND Position = ?", (gameId, pos)) if cursor.fetchone()[0] is not None: continue # Toggle pencil mark cursor.execute("EXEC sp_TogglePencilMark ?, ?, ?", (gameId, pos, pencilValue)) conn.commit() # Check if added or removed cursor.execute("SELECT IsActive FROM PencilMarks WHERE GameId = ? AND Position = ? AND PencilValue = ?", (gameId, pos, pencilValue)) is_active = cursor.fetchone()[0] last_action_type = "added" if is_active else "removed" last_is_active = is_active processed_positions.append(pos) cursor.close() conn.close() return json.dumps({ "status": "SUCCESS", "message": f"Pencil mark {pencilValue} {last_action_type} at position(s) {processed_positions}", "game_id": gameId, "positions_processed": processed_positions, "pencil_value": pencilValue, "is_active": last_is_active, "action_type": last_action_type, "action_time": datetime.now().isoformat() }) By parsing the JSON string, the UDF can now iterate through the list of positions and perform the required database operations for each one. This allows players to mark multiple cells with the same pencil value simultaneously. This approach is highly versatile and can be used as a template for any scenario where you need to pass multiple selected items from a Power BI report to a UDF. More in-depth details can be found at these blog posts: Translytical Sudoku Handling Multi-Row Inputs for Translytical Task Flows Hope this example is useful and sparks some ideas!Wallet Watch Analytics: Expense Tracker
Introduction: WalletWise Analytics is a smart personal expense tracking application that combines the power of Microsoft Fabric and Power BI to create an intelligent expense tracking system. What It Does: - Track Expenses in Real-time: Add your daily expenses directly from a Power BI dashboard and watch your balance update instantly - Monitor Account Balance: See your current balance on a live card that updates every time you spend money - Email Notifications: Get email alerts sent to your inbox when your balance gets dangerously low - Monthly Salary Management: Automatically adds $8,000 monthly salary to keep your account funded - Expense Control: View all your expenses in a table and visualize the spending - Write-back Functionality: Enter expense data through Power BI that immediately saves to your database Full Demo Video: Click Here Steps Used: Step 1: Database Setup - Created two tables in Fabric SQL Database: Expense table and Bank table - Added sample data to test the system Step 2: User Data Functions - Built smart functions that can add expenses, add salary, and send email alerts - Connected these functions to the database to read and write data Step 3: Power BI Dashboard - Created a user-friendly dashboard with cards showing balance and alerts - Added input controls where users can type expense details - Built a table showing all expenses Step 4: Write-back Buttons - Added "Add Expense" button that saves data to the database when clicked - Added "Add Salary" button that adds $8,000 monthly credit - Added "Send Email" button for email alerts Step 5: Smart Notifications - Set up email notifications for critical balance levels - Made the system update in real-time when changes happen Why I Came Up With This: I created this project to solve a common problem many people face: Losing track of their spending and running out of money unexpectedly. This project showcases the power of translytical flows - where analytical tools like Power BI can write data back to databases, creating a seamless experience between viewing data and updating it. UDF: Adding Expense import fabric.functions as fn from datetime import datetime udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="YOUR_CONNECTION_ALIAS") @udf.function() def write_expense(sqlDB: fn.FabricSqlConnection, expenseDate: str, description: str, amount: float, category: str) -> str: # Error handling if amount <= 0: raise fn.UserThrownError("Amount must be greater than 0", {"Amount": amount}) if len(description) > 255: raise fn.UserThrownError("Description too long (max 255 characters)", {"Description": description}) # Establish connection connection = sqlDB.connect() cursor = connection.cursor() try: # Insert into Expense table expense_query = "INSERT INTO Expense (ExpenseDate, Description, Amount, Category) VALUES (?, ?, ?, ?)" cursor.execute(expense_query, expenseDate, description, amount, category) # Insert corresponding debit into Bank table bank_query = "INSERT INTO Bank (TransactionDate, Description, Amount, TransactionType) VALUES (?, ?, ?, ?)" bank_description = f"Expense: {description}" cursor.execute(bank_query, expenseDate, bank_description, -amount, 'Debit') # Commit transaction connection.commit() return f"Expense ${amount} for '{description}' added successfully" except Exception as e: connection.rollback() raise fn.UserThrownError(f"Failed to add expense: {str(e)}") finally: cursor.close() connection.close() Add $8000 Monthly Salary @udf.connection(argName="sqlDB", alias="YOUR_CONNECTION_ALIAS") @udf.function() def add_salary(sqlDB: fn.FabricSqlConnection) -> str: connection = sqlDB.connect() cursor = connection.cursor() try: # Add fixed $8000 salary to Bank table with current date current_date = datetime.now().strftime('%Y-%m-%d') salary_query = "INSERT INTO Bank (TransactionDate, Description, Amount, TransactionType) VALUES (?, ?, ?, ?)" cursor.execute(salary_query, current_date, 'Monthly Salary', 8000.00, 'Credit') connection.commit() return "Monthly salary $8000 added successfully" except Exception as e: connection.rollback() raise fn.UserThrownError(f"Failed to add salary: {str(e)}") finally: cursor.close() connection.close() Send Email when balance drops below $100 Configuration Setup (Detailed Steps) Part A: Get SendGrid API Key Create SendGrid Account: - Go to https://sendgrid.com/ - Click "Start for Free" or "Sign Up" - Fill out registration form with your details - Verify your email address Access SendGrid Dashboard: - Log into your SendGrid account - You'll see the main dashboard Create API Key: - In the left sidebar, click "Settings" - Click "API Keys" - Click "Create API Key" button - Choose "Restricted Access" (recommended) - Give it a name like "Fabric-Expense-Tracker" - Under "Mail Send", select "Full Access" - Click "Create & View" - IMPORTANT: Copy the API key immediately (it won't be shown again) - Store it securely (you'll need this for the code) Part B: Update Your Function Code import fabric.functions as fn import logging from flask import Flask from flask_mail import Mail, Message from datetime import datetime udf = fn.UserDataFunctions() # Flask Mail Configuration app = Flask(__name__) app.config['MAIL_SERVER'] = 'smtp.sendgrid.net' app.config['MAIL_PORT'] = 587 app.config['MAIL_USERNAME'] = 'apikey' app.config['MAIL_PASSWORD'] = 'YOUR_SENDGRID_API_KEY_HERE' # Replace with your actual API key app.config['MAIL_USE_TLS'] = True app.config['MAIL_DEFAULT_SENDER'] = '[email protected]' # Replace with your email mail = Mail(app) @udf.connection(argName="sqlDB", alias="YOUR_CONNECTION_ALIAS") # Replace with your alias @udf.function() def check_balance_and_notify(sqlDB: fn.FabricSqlConnection, recipientemail: str) -> str: """Function to manually check balance and send notification if needed""" connection = sqlDB.connect() cursor = connection.cursor() try: # Get current balance balance_query = "SELECT SUM(Amount) FROM Bank" cursor.execute(balance_query) current_balance = cursor.fetchone()[0] or 0 if current_balance < 100: send_low_balance_email(current_balance, recipientemail) return f"🚨 CRITICAL: Balance is ${current_balance:.2f}. Email notification sent to {recipientemail}" else: return f"✅ Balance OK: ${current_balance:.2f}. No notification needed." except Exception as e: raise fn.UserThrownError(f"Failed to check balance: {str(e)}") finally: cursor.close() connection.close() def send_low_balance_email(balance: float, recipient_email: str): """Send low balance alert email - SIMPLIFIED VERSION""" try: current_time = datetime.now() # Simple text email instead of complex HTML email_text = f""" CRITICAL: Low Balance Alert! Your account balance has dropped below $100! Current Balance: ${balance:.2f} Date: {current_time.strftime('%Y-%m-%d')} Time: {current_time.strftime('%H:%M:%S')} Please add funds to your account immediately to avoid any issues. """ with app.app_context(): msg = Message( subject="🚨 CRITICAL: Account Balance Alert - Action Required", recipients=[recipient_email], body=email_text ) mail.send(msg) logging.info(f"Low balance email sent to {recipient_email}. Balance: ${balance:.2f}") except Exception as e: logging.error(f"Failed to send email: {str(e)}") raise fn.UserThrownError(f"Failed to send notification email: {str(e)}") Other Use Cases That Can Be Implemented: - Student Budget Manager: Track tuition, books, food, and living expenses for students - Travel Expense Tracker: Monitor trip expenses, daily spending limits, and travel budgets - Investment Portfolio Monitor: Track investments, gains, losses, and portfolio alerts - Healthcare Expense Tracker: Track medical bills, insurance claims, and health-related spendingVacation Tracker
Code Repo : https://github.com/NandanHegde15/MSFTFabric-Projects/blob/main/Vacation%20tracker/User%20Data%20Function.py Work Flow: The code implements a Vacation Tracker workflow in Microsoft Fabric using User Defined Functions (UDFs). It enables employees to submit and the Supervisor of that employee to approve, or reject vacation requests based on notification received. validates requests, checks public holidays, uses Azure OpenAI for recommendations, and sends notifications (via email + SMS). Employee │ │ (Submit Request: EmployeeId, StartDate, EndDate, Reason, Priority) ▼ Vacation Request UDF (vacation_tracker_request) │ ├─► Validate input (dates, reason, priority flag) │ ├─► SQL MERGE into VacationTracker │ ├─► Fetch Employee & Supervisor details from SQL │ ├─► Call Nager.Date API → Get holidays │ ├─► Query SQL → Past 12 months leave history │ ├─► Build prompt → Call Azure OpenAI GPT │ │ │ └─► AI Suggestion (approve/reject context) │ ├─► Notify Supervisor: │ • Email via Logic App (Approve / Reject buttons → Power BI report link) │ • SMS via Twilio (if priority = Yes) │ ▼ Supervisor Action (Approve / Reject) │ ├─► If Approve → vacation_tracker_approval UDF │ • Update SQL (status = Approved, reason, modified date) │ • Notify Employee (Approval Email via Logic App) │ └─► If Reject → vacation_tracker_rejection UDF • Update SQL (status = Rejected, reason, modified date) • Notify Employee (Rejection Email via Logic App) Employee Page : Governed by RLS (Every employee would see his/her own details and can raise a Vacation request) #Data Function 1 Supervisor would be able to see all details of his resources : Supervisor would receive an email and in case of High priority receive an SMS with AI sugesstion based on past data and public holiday in range : Based on Approve or Reject decision, Supervisor would be redirected to either Approval page or rejection page #Data Function 2 #Data Function 3 Based on approval/Rejection, Backend database and the Direct Query report would be updated and the employee would receive an email notification :Add Commentary to Financial Reports using Translytical
Business Need: Add commentary to live reports easily As a financial modeling and BI analyst, I serve business users who want to indicate the effect of market and external factors on some financial and operational metrics. It could be mentioning the impact of tariffs on 2025 cost of sales or that a lawsuit is the reason for a sudden stock price crash or a sudden change of vendor is the cause of a spike in inventory holding days. Before Translytical: Exporting Power BI reports to PDF or PowerPoint to add those comments Most business users end up doing this outside of Power BI. The few occasions we had tried to give them a way to put this comment into a DB has proven less instantaneous enough for the users, and they stopped using the DB based solution. They preferred to export the Power BI report to PDF or PowerPoint and then do their annotations and commentary. With Translytical: Users can now add comments and instantly see it displayed in their reports Translytical addresses the major need for instantly capturing the comments and unlimited updates to those comments. I find the business users of my reports feeling satisfied with the translytical based solutions. This Showcase: Add live commentary to financial reports on Power BI We will be building a solution that allows business users to add the comments they want to about different financial metrics they track for their investment monitoring and decision-making needs. The code and reports are deliberately made simple and easy to replicate so that you can see how easy it is to add these values without a lot of technical work. Needed Resources: Company Metrics table (provided in the attached Excel file) Metrics Order table (provided in the attached Excel file) Company Comments table (autogenerated via Dataflow Gen2 group by on the Company Metrics table, see the query codes below) You can easily use Dataflow Gen2 to load the two tables and generate the third table. I uploaded the file to a Lakehouse storage and then pointed Dataflow Gen2 to the file there. The M query codes are below: Metrics Order Table let Source = Lakehouse.Contents(null), Navigation = Source{[workspaceId = <your-workspace-id>]}[Data], #"Navigation 1" = Navigation{[lakehouseId = <your-lakehouse-id>]}[Data], #"Navigation 2" = #"Navigation 1"{[Id = "Files", ItemKind = "Folder"]}[Data], #"Navigation 3" = #"Navigation 2"{[Name = "translytical - source tables.xlsx"]}[Content], #"Imported Excel workbook" = Excel.Workbook(#"Navigation 3", null, true), #"Navigation 4" = #"Imported Excel workbook"{[Item = "Metrics Order", Kind = "Sheet"]}[Data], #"Promoted headers" = Table.PromoteHeaders(#"Navigation 4", [PromoteAllScalars = true]), #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"Metric", type text}, {"Position", Int64.Type}}) in #"Changed column type" Company Metrics Table let Source = Lakehouse.Contents(null), Navigation = Source{[workspaceId = <your-workspace-id>]}[Data], #"Navigation 1" = Navigation{[lakehouseId = <your-lakehouse-id>]}[Data], #"Navigation 2" = #"Navigation 1"{[Id = "Files", ItemKind = "Folder"]}[Data], #"Navigation 3" = #"Navigation 2"{[Name = "translytical - source tables.xlsx"]}[Content], #"Imported Excel workbook" = Excel.Workbook(#"Navigation 3", null, true), #"Navigation 4" = #"Imported Excel workbook"{[Item = "Company Metrics", Kind = "Sheet"]}[Data], #"Promoted headers" = Table.PromoteHeaders(#"Navigation 4", [PromoteAllScalars = true]), #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"ID", Int64.Type}, {"Company", type text}, {"Check", Int64.Type}, {"Metric", type text}, {"Year", Int64.Type}, {"Value", type number}, {"Comment", type text}}), #"Removed columns" = Table.RemoveColumns(#"Changed column type", {"Comment"}), #"Inserted Merged Column" = Table.AddColumn(#"Removed columns", "RelationshipKey", each Text.Combine({[Company], " ", [Metric]}), type text) in #"Inserted Merged Column" Company Comments Table let Source = Lakehouse.Contents(null), Navigation = Source{[workspaceId = <your-workspace-id>]}[Data], #"Navigation 1" = Navigation{[lakehouseId = <your-lakehouse-id>]}[Data], #"Navigation 2" = #"Navigation 1"{[Id = "Files", ItemKind = "Folder"]}[Data], #"Navigation 3" = #"Navigation 2"{[Name = "translytical - source tables.xlsx"]}[Content], #"Imported Excel workbook" = Excel.Workbook(#"Navigation 3", null, true), #"Navigation 4" = #"Imported Excel workbook"{[Item = "Company Metrics", Kind = "Sheet"]}[Data], #"Promoted headers" = Table.PromoteHeaders(#"Navigation 4", [PromoteAllScalars = true]), #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"ID", Int64.Type}, {"Company", type text}, {"Check", Int64.Type}, {"Metric", type text}, {"Year", Int64.Type}, {"Value", type number}, {"Comment", type text}}), #"Removed columns" = Table.RemoveColumns(#"Changed column type", {"Value", "Year", "Check"}), #"Grouped rows" = Table.Group(#"Removed columns", {"Company", "Metric", "Comment"}, {{"ID", each List.Max([ID]), type nullable Int64.Type}}), #"Reordered columns" = Table.ReorderColumns(#"Grouped rows", {"ID", "Company", "Metric", "Comment"}), #"Inserted Merged Column" = Table.AddColumn(#"Reordered columns", "RelationshipKey", each Text.Combine({[Company], " ", [Metric]}), type text) in #"Inserted Merged Column" Set the Dataflow Gen2 to load the tables into a Fabric SQL database. In my case I had created a Fabric SQL Database called translytical_sql so I simply pointed the Dataflow Gen 2 destination settings to that SQL DB and that it creates a new table for each. Now that the needed source tables are set up, we can move on to creating the user data function. After creating the function, ensure you add the SQL DB connection via managed connection and then you can use my code below. Don't bother trying to rename the default function that shows when you click "Add Function", it will change once you rename what's right after def in your code (the Python function name). Ensure the alias is same as your managed connection alias (see image above for mine). #insert_comment function import fabric.functions as fn udf = fn.UserDataFunctions() @udf.connection(argName='sqlDB', alias='translyticalsql') @udf.function() def insert_comment(sqlDB: fn.FabricSqlConnection, metricId: int, metric: str, company: str, comment: str) -> str: ''' Description: Inserts a comment into the SQL table of the financials. Args: sqlDB (fn.FabricSqlConnection): Fabric SQL database connection. metric_id (int): metric id (primary key). comment (str): the comment to add Returns: str: Confirmation message about data insertion. ''' # Set up the connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() # Insert the comment into the company metrics table add_comment_query = "UPDATE [dbo].[CompanyComments] SET [comment] = ? WHERE [ID] = ?;" cursor.execute(add_comment_query, (comment, metricId)) # Commit the transaction connection.commit() # Close the connection cursor.close() connection.close() return f'Financial metric "{metric}" for company "{company}" was updated with the following comment: {comment}' With the function done, you can now move to the final part: doing the Power BI report. I have attached the actual Power BI report. The major things to remember are: Enable translytical in preview settings Use Text slicer for the comment entry box Enter a name in the Text slicer's General Settings' title field. That name gets picked up in the button action settings. You can turn the title display off after that. Add a regular button as the clickable visual that triggers the function Set metricid in the action fields for the button to maximum [ID] for [CompanyMetrics][ID]. That's how my table structure is set up to work properly vis-a-vis the comment posting. You can set metric and company to first or last, doesn't matter. But use the fields from [CompanyMetrics] table Add a placeholder to the Text Slicer and a tooltip to the button. Lastly, disable the filter interaction of the "Metric for Comment" slicer on the main report table. And that's all! You can test on your Power BI desktop app before publishing to the web service. Enjoy your first/new business relevant translytical!Intelliventra – Smart Event Management Application
Intelliventra lets participants easily browse all sessions and add them to their personal agenda. Attendees can view session details such as time, room, track, and speaker. The page also allows attendees to add,remove sessions. It is designed to be simple, clear, and works for any event, from small workshops to large conferences.
Accounting Process Flow Automation by Translytical Flow
Sales Commission Process in a Marketing Company In an organization offering marketing solutions to lending institutions, the commission process for a sales order was complex, relying on shared spreadsheets with inputs and approvals from multiple departments. Implementing a live dashboard with a translytical flow streamlined the process, reducing the workflow time from 10 days to 2 days. Commissions are paid based on: # of sales appointments Other data from Zohobooks Approval from CFO Final Processing of Payment by finance/accountant With a set of dash boards powered by an SQL database with translytical functions , we made the recording of sales appointments and approvals at multiple steps and the subsequent sales commission payment kickoff and notification seamless. Direct query is used for quick reflection of the write backs (apointment times and apporval status changes). User data functions are called from power BI buttons to achieve this. Power automate flow is used to send email notifications which is invoked by a button click from dashbaord. The users were most thrilled because they could see the commission outflow immediately reflected in the Cashflow forecasts. Process Flow: Sales appointments enetered by respective sales people ->Approval from CFO ->Processing of sales commission payment by accountant and sending email notifications User Data Function used given here: import fabric.functions as fn import logging from datetime import datetime udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB1", alias="TWFDB") @udf.function() def create_appointment( sqlDB1: fn.FabricSqlConnection, fullname: str, startdate: str, starttime: str, durationminutes: int ) -> str: if sqlDB1 is None: raise fn.UserThrownError("Connection injection failed. Check alias 'TWFDB' in UDF connections.") # Validate / normalize if not fullname or fullname.strip() == "": raise fn.UserThrownError("Full name is required.") try: dur = int(durationminutes) except ValueError: raise fn.UserThrownError("Duration must be a whole number of minutes.") if dur <= 0: raise fn.UserThrownError("Duration must be > 0.") t = (starttime or "").strip() or "09:00:00" if t.count(":") == 1: t += ":00" elif t.count(":") != 2: raise fn.UserThrownError("Time must be HH:mm or HH:mm:ss.") try: start_datetime = datetime.strptime(f"{startdate} {t}", "%Y-%m-%d %H:%M:%S") except ValueError: raise fn.UserThrownError("Invalid date or time format. Expected YYYY-MM-DD and HH:mm[:ss].") created_date = datetime.now() conn = cur = None try: conn = sqlDB1.connect() conn.autocommit = False cur = conn.cursor() # Upsert salesperson cur.execute("SELECT SalespersonID FROM dbo.Salesperson WHERE FullName = ?", (fullname,)) row = cur.fetchone() if row: sp_id = int(row[0]) else: try: cur.execute( "INSERT INTO dbo.Salesperson (FullName) OUTPUT INSERTED.SalespersonID VALUES (?)", (fullname,) ) sp_id = int(cur.fetchone()[0]) except Exception: cur.execute("SELECT SalespersonID FROM dbo.Salesperson WHERE FullName = ?", (fullname,)) sp_id = int(cur.fetchone()[0]) statusvar = "Entry" # Insert appointment cur.execute( """ INSERT INTO dbo.Appointment (SalespersonID, StartDate, DurationMinutes, Status, CreatedDate) OUTPUT INSERTED.AppointmentID VALUES (?, ?, ?, ?, ?) """, (sp_id, start_datetime, dur, statusvar, created_date) ) appt_id = int(cur.fetchone()[0]) conn.commit() return f"✅ Appointment {appt_id} created for {fullname} at {start_datetime} ({dur} min)." except fn.UserThrownError: raise except Exception as e: logging.exception("create_appointment failed") try: if conn: conn.rollback() except: pass raise fn.UserThrownError(f"❌ Failed to create appointment: {str(e)}") finally: try: if cur: cur.close() except: pass try: if conn: conn.close() except: pass @udf.connection(argName="sqlDB1", alias="TWFDB") @udf.function() def approve_appointment_by_cfo( sqlDB1: fn.FabricSqlConnection, appointmentid: int ) -> str: if sqlDB1 is None: raise fn.UserThrownError("Connection injection failed. Check alias 'TWFDB' in UDF connections.") conn = cur = None try: conn = sqlDB1.connect() conn.autocommit = False cur = conn.cursor() # Check if appointment exists cur.execute("SELECT AppointmentID FROM dbo.Appointment WHERE AppointmentID = ?", (appointmentid,)) if not cur.fetchone(): raise fn.UserThrownError(f"No appointment found with ID {appointmentid}.") # Update status to CFOApproved cur.execute( """ UPDATE dbo.Appointment SET Status = 'CFOApproved' WHERE AppointmentID = ? """, (appointmentid,) ) conn.commit() return f"✅ Appointment {appointmentid} status updated to CFOApproved." except fn.UserThrownError: raise except Exception as e: logging.exception("approve_appointment_by_cfo failed") try: if conn: conn.rollback() except: pass raise fn.UserThrownError(f"❌ Failed to update appointment: {str(e)}") finally: try: if cur: cur.close() except: pass try: if conn: conn.close() except: passDynamic Power BI Experiences
I created a Power BI learning game to demonstrate how Translytical and UDF capabilities can transform user journey and user experience in Power BI to boost adoption. I focused on the following Translytical use cases: 1. Registration and State Management The game begins with an engagement action : players choose to start and see their email recognized. This creates commitment to the learning experience. A UDF populates the SQL database table with active players, notifications, timestamps, avatars, progress, and points. Using DAX to control transparency based on written columns, the registration menu "disappears" for active players but reappears for new users opening the Power BI report. 2. Notification Management I created personalized progress with guided steps to direct users through the prepared journey. Notifications attract player attention and explain game rules. This approach applies to any business report (eg: pushing notifications about report readiness, data accuracy, issues, guidance, etc. significantly boosts adoption). Users can dismiss notifications to avoid spam from already-consumed information. 3. Custom Time-Based Scoring To demonstrate this functionality, users are guided to click a specific item and select an avatar. Avatar selection triggers a UDF that calculates progression speed and assigns scores based on time spent. This enables progress tracking and maintains learning engagement. Result: These three combined UDFs showcase new possibilities for user experience, storytelling and user journey design, accessibility and engagement levels that boost adoption. The report is fully personalized based on each player's progress, allowing players to start from where they left off. This showcases an example of personalized journey tracking. Impact: While gamification provides an excellent confidence boost and aids change management, these techniques extend far beyond gaming scenarios. By combining state management, notification handling, and adaptive scoring, organizations can create engaging user journeys that drive adoption and sustained usage. The techniques showcased here provide a foundation for any scenario requiring user engagement, progress tracking, and personalized experiences. UDF used: TRANSLYTICAL GAMING FRAMEWORK - UDF #1 of 3 Purpose: User registration and state management for personalized Power BI experiences Business Value: Enables session persistence and user journey tracking insert_gaming_user import fabric.functions as fn from datetime import datetime # Create the UserDataFunctions instance udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="AlexSQL") @udf.function() def insert_gaming_user(sqlDB: fn.FabricSqlConnection, userPrincipalName: str) -> str: """ Insert a new gaming user record with default values including timestamp Args: sqlDB: Connection to the Fabric SQL database userPrincipalName: The user's principal name from DAX (e.g., from USERPRINCIPALNAME()) Returns: str: Success message for Power BI """ try: # Input validation if not userPrincipalName or userPrincipalName.strip() == "": raise fn.UserThrownError("User principal name cannot be empty.") # Clean the input userName = userPrincipalName.strip() # Get current timestamp for when UDF is executed current_timestamp = datetime.now() # Establish connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() # Check if user already exists (optional - remove if you want duplicates) check_query = "SELECT COUNT(*) FROM [dbo].[Gaming] WHERE [UserName] = ?" cursor.execute(check_query, userName) user_count = cursor.fetchone()[0] if user_count > 0: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' already exists in the gaming table.") # Generate next ID (since you're using INT NOT NULL PRIMARY KEY) # Get the maximum existing ID and add 1 id_query = "SELECT ISNULL(MAX([Id]), 0) + 1 FROM [dbo].[Gaming]" cursor.execute(id_query) next_id = cursor.fetchone()[0] # Insert the new gaming user record with extended columns including Points and Avatar ID insert_query = """ INSERT INTO [dbo].[Gaming] ( [Id], [UserName], [BeginID], [NotificationStart], [TimeStamp], [Level1], [L1_TimeStamp], [Level2], [L2_TimeStamp], [Points], [ChallengePoints], [Avatar ID] ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """ # Execute the insert with specified values # TimeStamp = current datetime, Points and ChallengePoints = 0, Avatar ID = 2, other new columns = NULL cursor.execute(insert_query, ( next_id, # Id userName, # UserName 1, # BeginID 0, # NotificationStart current_timestamp, # TimeStamp (current datetime) None, # Level1 (NULL) None, # L1_TimeStamp (NULL) None, # Level2 (NULL) None, # L2_TimeStamp (NULL) 0, # Points (set to 0) 0, # ChallengePoints (set to 0) 2 # Avatar ID (set to 2) )) # Commit the transaction connection.commit() # Close connections cursor.close() connection.close() return f"Successfully added user '{userName}' to gaming table with ID {next_id} and Avatar ID 2 at {current_timestamp.strftime('%Y-%m-%d %H:%M:%S')}" except fn.UserThrownError: # Re-raise user errors as-is raise except Exception as e: # Handle any other database errors raise fn.UserThrownError(f"Database error occurred: {str(e)}") TRANSLYTICAL GAMING FRAMEWORK - UDF #2 of 3 Purpose: Notification management and user engagement flow control Business Value: Enables dismissible notifications and prevents information spam, applicable to any business report requiring guided user experiences update_notification_status import fabric.functions as fn # Create the UserDataFunctions instance udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="AlexSQL") @udf.function() def update_notification_status(sqlDB: fn.FabricSqlConnection, userPrincipalName: str) -> str: """ Update the NotificationStart column to 1 for an existing user Args: sqlDB: Connection to the Fabric SQL database userPrincipalName: The user's principal name from DAX (e.g., from USERPRINCIPALNAME()) Returns: str: Success message for Power BI """ try: # Input validation if not userPrincipalName or userPrincipalName.strip() == "": raise fn.UserThrownError("User principal name cannot be empty.") # Clean the input userName = userPrincipalName.strip() # Establish connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() # Check if user exists check_query = "SELECT COUNT(*) FROM [dbo].[Gaming] WHERE [UserName] = ?" cursor.execute(check_query, userName) user_count = cursor.fetchone()[0] if user_count == 0: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' does not exist in the gaming table.") # Update the NotificationStart column to 1 update_query = """ UPDATE [dbo].[Gaming] SET [NotificationStart] = 1 WHERE [UserName] = ? """ # Execute the update cursor.execute(update_query, userName) # Commit the transaction connection.commit() # Close connections cursor.close() connection.close() return f"Successfully updated NotificationStart to 1 for user '{userName}'" except fn.UserThrownError: # Re-raise user errors as-is raise except Exception as e: # Handle any other database errors raise fn.UserThrownError(f"Database error occurred: {str(e)}") TRANSLYTICAL GAMING FRAMEWORK - UDF #3 of 3 Purpose: Custom time-based scoring and progress tracking with avatar personalization Business Value: Measures user engagement speed and rewards timely interactions, adaptable for training completion, task performance, and user adoption metrics complete_level1_challenge import fabric.functions as fn from datetime import datetime # Create the UserDataFunctions instance udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="AlexSQL") @udf.function() def complete_level1_challenge(sqlDB: fn.FabricSqlConnection, userPrincipalName: str, selectedAvatar: int) -> str: """ Complete Level 1 challenge, assign points based on time difference, and update Avatar ID Args: sqlDB: Connection to the Fabric SQL database userPrincipalName: The user's principal name from DAX (e.g., from USERPRINCIPALNAME()) selectedAvatar: The selected avatar ID from DAX measure Returns: str: Success message with points awarded for Power BI """ try: # Input validation if not userPrincipalName or userPrincipalName.strip() == "": raise fn.UserThrownError("User principal name cannot be empty.") if selectedAvatar is None: raise fn.UserThrownError("Selected Avatar cannot be empty.") # Clean the input userName = userPrincipalName.strip() # Get current timestamp for L1_TimeStamp current_timestamp = datetime.now() # Establish connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() # Check if user exists and get their initial TimeStamp check_query = """ SELECT [TimeStamp], [L1_TimeStamp] FROM [dbo].[Gaming] WHERE [UserName] = ? """ cursor.execute(check_query, userName) result = cursor.fetchone() if not result: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' does not exist in the gaming table.") initial_timestamp = result[0] existing_l1_timestamp = result[1] # Check if Level 1 challenge already completed if existing_l1_timestamp is not None: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' has already completed Level 1 challenge.") # Check if initial TimeStamp exists if initial_timestamp is None: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' does not have an initial TimeStamp.") # Calculate time difference time_diff = current_timestamp - initial_timestamp total_minutes = time_diff.total_seconds() / 60 # Determine points based on time difference # GAMING MECHANICS: Time-based scoring rewards faster completion # < 10 min = 20 pts (expert), < 1 hour = 10 pts (good), etc. if total_minutes < 10: points = 20 time_category = "< 10 minutes" elif total_minutes < 60: # < 1 hour points = 10 time_category = "< 1 hour" elif total_minutes < 1440: # < 1 day (24 hours * 60 minutes) points = 5 time_category = "< 1 day" else: # more than 1 day points = 3 time_category = "> 1 day" # Update L1_TimeStamp, Level1 points, and Avatar ID update_query = """ UPDATE [dbo].[Gaming] SET [L1_TimeStamp] = ?, [Level1] = ?, [Avatar ID] = ? WHERE [UserName] = ? """ # Execute the update cursor.execute(update_query, (current_timestamp, points, selectedAvatar, userName)) # Commit the transaction connection.commit() # Close connections cursor.close() connection.close() return f"Level 1 completed for '{userName}': {points} points awarded ({time_category}), Avatar ID updated to {selectedAvatar} at {current_timestamp.strftime('%Y-%m-%d %H:%M:%S')}" except fn.UserThrownError: # Re-raise user errors as-is raise except Exception as e: # Handle any other database errors raise fn.UserThrownError(f"Database error occurred: {str(e)}") If this Translytical entry resonates with you, I'd love to connect! Please give it a ❤️ if you found it valuable, and feel free to reach out on LinkedIn Alexandru Badiu | LinkedIn Best regards, Alexandru BadiuIoT Defect Tracking and Resolution from PBI report
Scenario description: IoT-Driven Maintenance with Power BI Writeback Overview In this workflow, industrial equipment is fitted with IoT sensors streaming operational metrics (temperature, vibration, pressure, etc.) into a real-time event stream. An event processor (Activator) continuously evaluates incoming readings against expected patterns. If a defect or anomaly is detected, the system: Sends an instant notification to the maintenance worker’s mobile device. Creates a new defect tracking record in the operational SQL database with initial details. After performing the repair, the maintenance worker accesses a Power BI maintenance dashboard. Here, they can: Review the defect record (with sensor data snapshots, alerts, and location). Document the defect resolution, root cause, and parts replaced. Submit this information directly back to the SQL defect tracking table via a User Data Function (UDF). The UDF securely updates the operational database in real time, eliminating the need for separate maintenance forms or manual syncing. This ensures that historical defect data stays complete, accurate, and available for both operational and analytical purposes. Here is the code used for this scenario to update the defect after resolving it through workforce: import fabric.functions as fn udf = fn.UserDataFunctions() # =============================== # UDF: update_defect_resolution # =============================== # Take a defect id, resolver, resoltuion notes and parts replaced (Status will be set automatically to "closed" as this is the intended action) as input parameters # Write the data back to the SQL database # Users will provide these parameters in the PowerBI report @udf.connection(argName="sqlDB",alias="<Your DB alias from connections>") @udf.function() def update_defect_resolution(sqlDB: fn.FabricSqlConnection, defectid: int, resolvedby: str, resolutionnotes: str, partsreplaced: str) -> str: """ Updates defect tracking record in the operational SQL database. Status is default set to "Closed" if a resolution is entered. Parameters: ----------- defectid : int Unique identifier of the defect record. resolvedby : str Name or ID of the maintenance worker. resolutionnotes : str Description of how the defect was resolved. partsreplaced : str Comma-separated list of parts replaced. Returns: -------- str "Defect resolution updated successfully." """ try: # Establish a connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() # Update query query = """ UPDATE [maintenance_demo].[defect_tracking] SET resolved_by = ?, resolution_notes = ?, parts_replaced = ?, status = 'Closed', resolution_timestamp = GETUTCDATE() WHERE defect_id = ? """ # Execute update cursor.execute(query, (resolvedby, resolutionnotes, partsreplaced, defectid)) # Commit the transaction connection.commit() cursor.close() connection.close() return "Defect resolution updated successfully." except Exception as e: return { "defect_id": defectid, "status": "Error", "message": str(e) } Feel free to use this code as inspiration for your own scenarios!
