<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Koleti Swathi on Medium]]></title>
        <description><![CDATA[Stories by Koleti Swathi on Medium]]></description>
        <link>https://medium.com/@koleti.swathi?source=rss-362236f45b44------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*jfXhsCK_woGMuM8q</url>
            <title>Stories by Koleti Swathi on Medium</title>
            <link>https://medium.com/@koleti.swathi?source=rss-362236f45b44------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 22:27:11 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@koleti.swathi/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[How I Cut Power BI Refresh Time from 10 Minutes to 4 Using Temp Tables and Indexes]]></title>
            <link>https://medium.com/@koleti.swathi/how-i-cut-power-bi-refresh-time-from-10-minutes-to-4-using-temp-tables-and-indexes-5c21bb75e04f?source=rss-362236f45b44------2</link>
            <guid isPermaLink="false">https://medium.com/p/5c21bb75e04f</guid>
            <dc:creator><![CDATA[Koleti Swathi]]></dc:creator>
            <pubDate>Tue, 15 Jul 2025 10:54:43 GMT</pubDate>
            <atom:updated>2025-07-15T10:54:43.925Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>The Problem Started in Power BI — But It Wasn’t a DAX Issue</strong></p><p>A while ago, I was working on a Power BI report where one particular visual kept lagging badly. End users complained that it was “stuck,” and even the refresh sometimes timed out.</p><p>Like most Power BI developers, I started looking at <strong>DAX</strong>, suspecting heavy filters or expensive measures.</p><p>But here’s the twist — <strong>the slowness wasn’t in DAX.</strong><br> It was happening because the visual called a <strong>SQL stored procedure</strong> in the backend.</p><p><strong>Digging Into the Stored Procedure</strong></p><p>The stored proc pulled data from a large SalesData table with millions of rows. It was designed to return <strong>monthly totals by region</strong>, and the logic looked like this:</p><p>Stored Proc (CTE version — looks clean):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/489/1*skjryAyeJvb9xF4eTYpJrQ.png" /></figure><p><strong>What Went Wrong?</strong></p><p>The logic looks fine — even clean. But it had a hidden cost:</p><p><strong><em>CTEs (Common Table Expressions) are just query fragments.</em></strong><em><br> They don’t store data. Every time you call them — especially inside a </em><em>WHILE loop they get </em><strong><em>re-evaluated</em></strong><em>.</em></p><p>And with <strong>12 iterations</strong> on a <strong>large table</strong>, this meant:</p><ul><li>12 full scans on SalesData</li><li>High CPU and I/O usage</li><li>Unnecessary repetition</li></ul><p><strong>Total Runtime:</strong> Over 10 <strong>minutes</strong></p><p>The Fix: Temp Tables and Indexing</p><p>After diagnosing the re-evaluation issue, I refactored the stored procedure like this:</p><p>Optimized Stored Proc (Temp Table + Index)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/507/1*7PjrmhdXmRYk9J7U8R-5Gw.png" /></figure><h3>Why It Works:</h3><ul><li><strong>Temp Table</strong>: Stores intermediate data that’s reused, not recalculated</li><li><strong>Clustered Index</strong>: Speeds up lookups when reading the result</li><li><strong>Reduced Scans</strong>: Only 12 small inserts, not 12 full scans</li></ul><h3>When to Use What</h3><p><strong>Clean one-off logic → Use a CTE</strong><br> Great for breaking down complex queries or recursion. Ideal when you don’t need to reuse the result — keeps the SQL readable.</p><p><strong>Looping or reusing data → Use a Temp Table</strong><br> When the same logic/data needs to be accessed multiple times inside a loop or across query steps, temp tables are more efficient than recalculating.</p><p><strong>Speeding up joins or filters → Use an Index</strong><br> Indexes (especially on large tables or temp tables) help SQL find matching rows faster — reducing scan time and improving join performance.</p><p><strong>Avoiding repeated table scans → Use Temp Table + Index</strong><br> Store the result once, index it, and reuse it as needed. This combo is especially powerful inside stored procedures or large reporting queries.</p><p>Have you ever been misled by “clean SQL” that turned out to be a performance trap?</p><p>If you’re using <strong>Power BI with large SQL procedures</strong>, this might be your wake-up call — like it was for me.</p><p>Feel free to share your experience or drop a comment if you’ve faced a similar situation.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5c21bb75e04f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Day Our Gateway Connection Broke — A Pitfall That Taught Us the Right Way to Connect On-Prem…]]></title>
            <link>https://medium.com/@koleti.swathi/the-day-our-gateway-connection-broke-a-pitfall-that-taught-us-the-right-way-to-connect-on-prem-ea3db2c6d597?source=rss-362236f45b44------2</link>
            <guid isPermaLink="false">https://medium.com/p/ea3db2c6d597</guid>
            <dc:creator><![CDATA[Koleti Swathi]]></dc:creator>
            <pubDate>Tue, 01 Jul 2025 07:14:13 GMT</pubDate>
            <atom:updated>2025-07-01T07:14:13.769Z</atom:updated>
            <content:encoded><![CDATA[<h3>The Day Our Gateway Connection Broke — A Pitfall That Taught Us the Right Way to Connect On-Prem to the Cloud</h3><h4>It started like any other day on the project.</h4><p>We had a task: connect our on-premises MySQL database to <strong>Microsoft Fabric</strong> using the <strong>on-prem data gateway</strong>, so we could build some dashboards in Power BI.</p><p>Straightforward, right?</p><p>That’s what we thought too.</p><h4>Step 1: Setting Up the Connection — Or So We Thought</h4><p>We opened Microsoft Fabric, clicked on <strong>Create New Connection</strong>, selected <strong>MySQL</strong>, and entered these details:</p><ul><li>Host: localhost</li><li>Port: 3306</li><li>Username: root</li><li>Password: ✅</li></ul><p>Hit <strong>Test Connection</strong>…</p><p>And BAM 💥 — *”Internal connection fatal error.”</p><p>At first, we thought it was a network issue. We checked firewall rules, confirmed the gateway was online, restarted services…</p><p>Still the same error.</p><h4>Step 2: What’s Wrong With root?</h4><p>While reading through Microsoft documentation, we found this:</p><p><em>The </em><em>root user is a </em><strong><em>system-level</em></strong><em> account. It has all privileges — great for development — but risky for production or secure environments.</em></p><p><em>Microsoft Fabric’s </em><strong><em>on-prem gateway</em></strong><em> treats </em><em>root as an </em><strong><em>internal/system user</em></strong><em>, and often blocks or restricts it by default.</em></p><p>Supporting References:</p><ul><li><a href="https://community.fabric.microsoft.com/t5/Service/On-Premesis-Gateway-cannot-connect-to-MySQl-source/m-p/71709">Power BI Community: Gateway blocks root user</a></li><li><a href="https://learn.microsoft.com/en-us/fabric/data-factory/data-source-management">Microsoft Docs: Gateway data source management</a></li></ul><h4>Step 3: Creating a Safe, Dedicated DB User</h4><p>So, we created a new MySQL user:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/895/1*TL2IPuntXJyvXxcKS7lu-w.png" /></figure><p>We updated the connection in Fabric to use fabric_user, tested it...</p><p>And it worked perfectly! 🎉</p><p>But the story doesn’t end here.</p><h4>Step 4: The Second Database Puzzle</h4><p>A few days later, we needed to connect to another database analytics_db.</p><p>Since fabric_user worked for the first DB, we tried using the <strong>same connection</strong>.</p><p>Boom. Another error.</p><p>Everything looked fine — network, credentials, gateway. So what was wrong this time?</p><p><strong>Step 5: One User ≠ Access to All Databases</strong></p><p>Here’s the catch: <em>Just because a user exists doesn’t mean it has access to </em><strong><em>all</em></strong><em> databases.</em></p><p>In our case, fabric_user had permission <strong>only</strong> on sales_db. Trying to use it for analytics_db was like using the wrong key for a locked room.</p><h4>Step 6: Granting Access to Another Database</h4><p>We had two ways to fix this:</p><p>Option A: Give the same user access to the second DB</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/865/1*8BKjsBXz48bMd_6VHeaNrQ.png" /></figure><p>Now, the same user could access both databases.</p><p>Option B: Create a second user for the second DB</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/897/1*YYryCu18jlk_wLTSCWXFmA.png" /></figure><p>Then, we created a new connection in Fabric using this user. Both approaches work — it depends on your security policy.</p><h4>Bonus Tip: Can We Use Dynamic Database Names in Gateway Connections?</h4><p>We had this question too..</p><h4>❌ Short answer: No, not directly.</h4><p>Here’s why:</p><ul><li>The database name is part of the connection string.</li><li>Fabric and Power BI gateway connections are <strong>bound to a specific database</strong>.</li><li>Changing it at runtime is <strong>not supported</strong> for security and caching reasons.</li></ul><h4>What to do instead:</h4><ul><li>Create <strong>separate connections</strong> for each DB.</li><li>Or use the <strong>same user</strong> and grant access to all required databases.</li></ul><p><strong>Final Checklist: Before Creating Gateway Connections</strong></p><p>✅ Don’t use root — it’s blocked.</p><p>✅ Create a new user with access to <strong>specific</strong> databases.</p><p>✅ Check that the user has privileges (GRANT) on the target DB.</p><p>✅ If accessing multiple DBs, either: Grant the same user access to all, or Create separate users and connections.</p><p>✅ Ensure your gateway is online and port 3306 is open.</p><p>❌ Don’t try to pass dynamic DB names — it won’t work.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ea3db2c6d597" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Power BI + MySQL Database: Connecting with Stored Procedures, Views, and Functions Made Simple]]></title>
            <link>https://medium.com/@koleti.swathi/power-bi-mysql-database-connecting-with-stored-procedures-views-and-functions-made-simple-1b74c5f01b57?source=rss-362236f45b44------2</link>
            <guid isPermaLink="false">https://medium.com/p/1b74c5f01b57</guid>
            <dc:creator><![CDATA[Koleti Swathi]]></dc:creator>
            <pubDate>Fri, 18 Apr 2025 13:10:01 GMT</pubDate>
            <atom:updated>2025-04-18T13:18:41.362Z</atom:updated>
            <content:encoded><![CDATA[<p>Power BI is powerful for building dashboards and reports. But what if your data is in <strong>MySQL </strong>and you want to use <strong>Stored Procedures</strong>, <strong>Views</strong>, or <strong>Functions</strong>?</p><p>In this guide, we’ll walk through how to Connect MySQL Database to Power BI</p><ul><li>Use <strong>Stored Procedures with and without Parameters</strong></li><li>Use <strong>Views</strong></li><li>Use <strong>Functions</strong></li></ul><p>Let’s go step by step using a simple scenario using <strong>Pizza_data </strong>dataset.</p><ol><li><strong>Stored Procedure WITHOUT Parameter</strong></li></ol><p>I create a store procedure for Commutative Revenue of pizza sales with out passing parameter.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/844/1*nVdDVbiupNGqRsB6sLp_Cg.png" /><figcaption>Store Procedure</figcaption></figure><p>once we executed the store procedure we can call and get the results as many times as well .</p><p>Now, we will see, How to Use this procedure and get load cumulative revenue in to Power BI:</p><ol><li>Open Power BI Desktop.</li><li>Go to <strong>Home &gt; Get Data &gt; My SQL Database</strong>.</li><li>Enter your <strong>server name</strong> and <strong>database name</strong>.</li><li>Click on <strong>Advanced options</strong>, and type:</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/581/1*0EUCY887GwFde3YDlwAYFA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/854/1*MBmlTGSpkW4ti4GZGRAWpA.png" /></figure><p>next you can load or transform data based on priorities.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/335/1*__pg8ENQg2iDvr5OP14_uA.png" /></figure><p><strong>2. Stored Procedure WITH Parameter</strong></p><p>I created a stored procedure like category wise topn pizzas and passing the topn as parameter.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*LlMJo2u0q68tm0_UsubGaw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3kxdfQ5wU4hPunsl_kVGog.png" /></figure><ol><li>Enter your <strong>server name</strong> and <strong>database name</strong>.</li><li>Click on <strong>Advanced options</strong>, and type:</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*6PMAgDAbLAE2jKsasBOJ6A.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*s6097hWxqbeg3aT_RrMLuQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/357/1*_1DkRrwGSp8xsuJZh54X3w.png" /><figcaption>Top5 revenue category wise</figcaption></figure><p>Currently, we are able to get the top 5 items per category. But if we want to change it to top 10, we would have to download the data again. Instead, we can make it dynamic by allowing the user to select the Top N value directly.</p><p>Now, let’s explore how real-time functionality works by passing static parameters that remain fixed during data load.</p><p>By achieving static parameter selection, we need to create the parameter then set the parameter value in m code , now see how we can do…</p><p>step1: create a parameter in power query under the manage parameter as new parameter.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/176/1*xKaWJwfEXniLWwVqCz1GZg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/371/1*d7BzvhrhWqGznb5KeDfyAA.png" /></figure><p>once you create it will appear as follow..</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/628/1*6vs8_i5uY0H4lKT2Rpq60g.png" /></figure><p>Step 2: Now, we need to add the parameter to the stored procedure. In Power BI, go to the <strong>Advanced Editor</strong> and update the M code to use this parameter.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/725/1*o9dy4zlsJB_tG2MQZJof1Q.png" /></figure><p>First, we create a variable called TopN and give it the value from the parameter we made. Since Power BI only supports decimal numbers for parameters, we use Number.Round to make sure the value is a whole number so it works correctly in our stored procedure.</p><p>Then, we add this TopN value into our SQL query by turning it into text using Text.From(TopN). This is important because Power Query doesn’t let you mix numbers and text when building a query — it will give an error. So, we convert the number to text so it fits into the SQL command properly.</p><p>After updating the M code, click “Close &amp; Apply” to load the data into Power BI. You can now test how the data changes by selecting a different number in the parameter (like Top 5 or Top 10) — this lets you control it statically without editing the code again.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/474/1*843SXyLBmd9yVu6UShzH6A.png" /></figure><p>here we have top1 category but now i want top5 then you go the transform and select edit parameter than you give the number as 5</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/203/1*T7sL92Kr2acyXXMux9lmKw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/411/1*oJFj_O0ljJELTzbYton9cg.png" /></figure><p>then automatically the top5 category values imported automatically.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/575/1*kqDTcGT4a_Bwq353iRTptw.png" /></figure><ul><li><strong>Faster and Lighter Reports:</strong><br> Dynamic loading brings only the data you need (like Top 5 or Top 10 items), so your report loads faster and runs more smoothly.</li><li><strong>Easy to Change:</strong><br> You can easily change how much data to load just by picking a number — no need to rewrite the query each time.</li></ul><p><strong>3.Getting Data from MySQL Views into Power BI</strong></p><p>In here i created a view as category wise order quantity as follows,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/642/1*81dr0E0hK7doaANLYIYrqg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/633/1*OaIeS8Y95eT0sHlVtQCxKA.png" /></figure><p>then go to power bi and get this view into power bi,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/469/1*-mt1cmKR3qQ2wh8KfvBeJg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/419/1*OnLQj-XQdJ8NPa8Vdvi-_A.png" /></figure><p>Earlier, we passed a Top N value as a parameter to load only the top items. In the same way, we can create a category list and use it as a parameter to choose which category we want to load.</p><p>step 1 : In here our view have list of categories as follow</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/197/1*IDMS7xExgh3yYWoQ40v2mg.png" /></figure><p>I’m creating a parameter to filter data directly at the source level. For example, if I only want to work with data from the ‘Classic’ category, this filter will make sure that only ‘Classic’ data gets loaded into Power BI and shown in the visuals.</p><p>Step1: create a parameter for categories of list values</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/435/1*AJjWebm5vmiiTM9tz51PiQ.png" /></figure><p>step 2: pass this parameter into M code of your view query</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/804/1*OwR24pg1P_gHO9YaNtOerA.png" /></figure><p>In here param_of_category_list is my parameter and i created a category Filter as variable then close and apply will see, how the list parameter work,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/412/1*CR74gAzNEd4oM2ZCZIMe9Q.png" /></figure><p>if we edit the parameters in power bi and selected as only classic category so, i get only classic data</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/268/1*sTYCDrqEFrnrpZdpdbvrGA.png" /></figure><p>For demo purposes, I created a view with just two columns to keep it simple. I only got the ‘Classic’ category data because I applied the filter directly at the source level — not in the data model. So, only the filtered data is loaded into Power BI.</p><p>lets and check the data in power query now,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/760/1*CPf60n3Jmh_K2jv4kq0gGQ.png" /><figcaption>this parameter filtered the data at source level not in model</figcaption></figure><p>Let’s say your business operates globally, but you only want to analyze data for a specific region like India. In real-world scenarios with millions of records, we often filter the data at the source level to improve performance. It all depends on your project requirements.</p><p>let’s take another view from different table( ele_store table) for implementing parameter</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/340/1*b7Gbj5xweHTQaZ82cbsw7w.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/365/1*moaul5gzR7cOFgRJMFXhcw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wq6AEz1zt0vEOgPPtBEnpQ.png" /><figcaption>View Table</figcaption></figure><p>step 1: create a parameter for region</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/602/1*5Ztxlj_R_16Y3-F0iRlvtw.png" /></figure><p>I created a blank query and added a list of regions in it. Then, I used that query as the source when creating my parameter and update the parameter in M code.</p><p>Now see how the we can filter the region at the source level data</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/477/1*11Fy2wwFc9kJAA0Pi46yQA.png" /><figcaption>selected Asia</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/551/1*phTEZruyHjnG2_aaV6L0YA.png" /></figure><p>here I got only Asia data which is filtered at source level not model level.</p><p><strong>3.How to use MySQL Functions and load the data into Power BI</strong></p><p>In this real-life example, we have customer data that includes sensitive information like <strong>SSNs (Social Security Numbers)</strong> and <strong>Email IDs</strong>. To keep this private and safe, we will <strong>hide (mask) these details right in the database</strong>, before sending the data to Power BI. We will do this by creating <strong>small MySQL functions</strong> that change how the SSN and Email look (so the full details aren’t shown). Then, we’ll create a <strong>view</strong> that shows only the masked data. Power BI will use this view, so no private information will be shown in reports.</p><p>We will <strong>start from scratch</strong> and show how to <strong>set up everything step by step</strong>, including how to hide sensitive data using MySQL functions and how to use that data safely in Power BI.</p><p>step1: let’s create a customer table and insert into some data first.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/466/1*8pjhLaiwRJW5x-_do7lgLQ.png" /><figcaption>Customers Table</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/875/1*2k4wudxuEtRHpQ5wdf1fuQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/471/1*jdqP9DVokGPv-q8z0Fsjgg.png" /><figcaption>Customer Data</figcaption></figure><p>In our customer data, we need to hide the email and SSN columns. To do this, we will first create a <strong>view</strong> and then mask the data in that view. This way, the original data will stay unchanged.</p><p>step 2: create a view for our customer data</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/369/1*waCMj5cQV8ueeEQkWQ47Iw.png" /></figure><p>step 3: create a functions for hide email data and ssn</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/890/1*o3Q71MQ4xWgJcpaGU5G1EA.png" /></figure><p>Note: What does SUBSTRING_INDEX(p_email, &#39;@&#39;, -1) do?</p><p>It takes the email and splits it at the @ symbol.</p><p>Ex:p_email = &#39;john.smith@email.com&#39; -&gt; jo***@***@email.com</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/540/1*7ovR1pp6bOvCcSbWoXvyXg.png" /></figure><p>When we use both the <strong>email masking</strong> and <strong>SSN masking</strong> functions, we can see how the final masked result looks.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/510/1*T1sPBURCY49R9mCRB8XvHw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/248/1*q9D1V_Rzpjn0v0oolgEMKQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/378/1*cQ41bBhfKl-YfRRR-EmstA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/188/1*iBHL1Gby-nBu-pyVgKaWMQ.png" /></figure><p>So, one important thing to understand is that we <strong>can’t use these scalar functions directly in Power BI</strong>, because they return only <strong>one value at a time</strong>, not a full column. We also <strong>can’t pass a full column</strong> as a parameter to a scalar function in Power BI.</p><p>But we <strong>can use these functions inside a view or stored procedure</strong>, where we apply the function to each row in a table.</p><p>So in our case, we use these two functions <strong>inside a view</strong> to mask the data, and then <strong>load that view into Power BI</strong>. This way, the sensitive data is already masked before it reaches Power BI.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/583/1*LGQ-wBpp9hl5CxI_JuVeWQ.png" /></figure><p>let’s see the view data,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/479/1*JoedqMfgLc0nFM5C53DctA.png" /></figure><p>let’s load this data into power bi and check whether it able to work or not.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/784/1*L6H3hb_gyA28LpmQ-7hs0g.png" /></figure><p><strong>1. View</strong></p><p><strong>Use when:</strong> You want to show a pre-defined table or filtered data from one or more tables.<br> <strong>Why:</strong> Views are like virtual tables. They help you clean, join, or filter data before it comes into Power BI.</p><p><strong>Example:</strong><br> You want only pizza orders from 2024 → create a view that filters for 2024 orders.</p><p><strong>2. Stored Procedure</strong></p><p><strong>Use when:</strong> You need to perform complex logic, calculations, or actions before loading data.<br> <strong>Why:</strong> Stored procedures are powerful. They can take <strong>parameters</strong>, do <strong>calculations</strong>, and return a final result for Power BI.</p><p><strong>Example:</strong><br> You want to load pizza sales for a selected date range → use a stored procedure with <strong>start date</strong> and <strong>end date</strong> as parameters.</p><p><strong>3. Function</strong></p><p><strong>Use when:</strong> You want to return a single value or use it inside a view or stored procedure.<br> <strong>Why:</strong> Functions are good for small tasks like masking data, formatting text, or calculating a value.</p><p><strong>Example:</strong><br> You want to hide customer phone numbers → use a function to mask part of the number.</p><p>Note: When working with MySQL (like in SQL Workbench), there is a limitation — you cannot return a table from a function. MySQL only supports scalar functions, which means the function can return just a single value like a number, string, or date, but not a full table with multiple rows. So, if you want to return a set of results using a function, it won’t work in MySQL. Instead, you can use a stored procedure or a view to achieve that. However, if you’re using SQL Server, you can create table-valued functions, which do allow you to return a full table from a function. These are very useful in Power BI, as they help you reuse logic and filter data directly at the source. For example, you can create a function like fn_GetPizzaOrdersByDate(@StartDate, @EndDate) in SQL Server to get all pizza orders between two dates, and then Power BI can connect to this function and load the filtered data.</p><p>Thanks for reading!!</p><p>So far, we’ve only used static parameters. In my next article, we’ll explore how to use dynamic parameters as slicers in Power BI to filter data directly at the source level, helping you create faster and more efficient reports.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1b74c5f01b57" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Beware! The Hidden Trap of Using DATESINPERIOD with Historical vs. Present Data in Power BI]]></title>
            <link>https://medium.com/@koleti.swathi/beware-the-hidden-trap-of-using-datesinperiod-with-historical-vs-present-data-in-power-bi-13ef3874eabb?source=rss-362236f45b44------2</link>
            <guid isPermaLink="false">https://medium.com/p/13ef3874eabb</guid>
            <dc:creator><![CDATA[Koleti Swathi]]></dc:creator>
            <pubDate>Tue, 08 Apr 2025 12:51:56 GMT</pubDate>
            <atom:updated>2025-04-08T12:51:56.277Z</atom:updated>
            <content:encoded><![CDATA[<p>Time intelligence functions like <strong>DATESINPERIOD</strong> are useful for calculating moving averages, running totals, and comparing data across different time periods in Power BI and DAX.</p><p>However, many users make a common mistake: mixing <strong>dynamic dates</strong> (like today’s date) with <strong>static historical data</strong>. This can lead to errors or blank results because the function may try to reference dates that don’t exist in your dataset.</p><p>In this article, we’ll explain how to avoid this issue and use these functions correctly.</p><ul><li>How DATESINPERIOD behaves differently with historical vs. live data.</li><li>Why TODAY() can be dangerous in historical analysis.</li><li>The critical role of a well-structured Date Dimension table.</li></ul><h3>How DATESINPERIOD Works</h3><p>The DATESINPERIOD function returns a table of dates based on a starting point and a specified interval:</p><pre>DATESINPERIOD(&lt;dates&gt;, &lt;start_date&gt;, &lt;number_of_itervals&gt;, &lt;interval&gt;)</pre><p>For example:</p><pre>DATESINPERIOD(&#39;Date&#39;[Date], DATE(2020,12,31), -12, MONTH)</pre><p>This returns the previous 12 months from December 31, 2020.If your Date table doesn’t cover the range you’re requesting, you may get unexpected blanks.</p><p><strong>Historical vs. Current Data — Why It’s Important to Know the Difference</strong><br> This is where things can get confusing. for suppose when you’re looking at historical data and your data is from a fixed time in the past (like sales from 2020 to 2024)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Krr3ZebtXd9lXsSxqi0nNw.png" /><figcaption>Ele_Store Data</figcaption></figure><p>let’s take sample data there we find the last 2 years sales…</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/408/1*eGrPLB9H07icGtt0YG9H3w.png" /></figure><p>we get the result accurately with out blanks of rows.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/337/1*ULiVMWsdsRWg-smSf9x-hw.png" /></figure><p>But there is risky approach: Using TODAY() with Historical Data</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/314/1*4qnkO95_448adCA-ZAYQxQ.png" /><figcaption>Using Today() function</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/506/1*4oOyqAnrU8xTNLtidyn2TQ.png" /><figcaption>The results are getting blanks</figcaption></figure><p><strong>The Trap:</strong></p><ul><li>TODAY() <strong>is dynamic</strong> and changes daily.</li><li>Your calendar is <strong>static (2020–2024)</strong>.</li><li><strong>Once we reach 2025, all </strong><strong>TODAY()-based calculations break silently</strong> because:</li><li>Power BI won’t error — it just returns blank.</li><li>This is why LASTDATE(DATE) is safer for historical analysis.</li></ul><h3>When TODAY() is Safe to Use</h3><p>If you’re working with <strong>real-time data</strong>, and your Date table extends into the future (2025+), then using TODAY() makes sense. This is common in:</p><ul><li>Dashboards updated daily</li><li>Operational KPIs</li><li>Live monitoring solutions</li></ul><p>Best Practice:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hrDp3TA1mThMibqP8fD_Tg.png" /></figure><h3>Your Date Table: The Backbone of Time Intelligence</h3><p>All time intelligence functions — DATESINPERIOD, SAMEPERIODLASTYEAR, DATEADD, PARALLELPERIOD, etc. — depend on a solid <strong>Date Dimension</strong> table.</p><h4>Your Date table should:</h4><ul><li>Be <strong>continuous</strong> (no missing dates)</li><li>Be <strong>marked as a Date Table</strong> (Model View → “Mark as Date Table”)</li><li>Include Year, Month, Quarter, Fiscal info, etc.</li><li>Extend <strong>before and after</strong> your actual data range</li></ul><p>If your Date table is incomplete or not properly configured, expect quiet errors and misleading results.</p><h4>Final Thought</h4><p>Time intelligence is one of DAX’s most powerful feature— <strong>but only when used with care</strong>. Avoid mixing dynamic functions like TODAY() with historical datasets, and make sure your Date Dimension is properly structured and marked.</p><h4>Have You Been Caught by This Trap?</h4><p>Ever spent hours debugging blank visuals caused by TODAY() in historical data models?<br> Share your experience in the comments — your lesson could help someone else!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=13ef3874eabb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Understanding AVERAGE, AVERAGEX, and AVERAGEA in Power BI: Differences, Use Cases & Real-Time…]]></title>
            <link>https://medium.com/@koleti.swathi/understanding-average-averagex-and-averagea-in-power-bi-differences-use-cases-real-time-9139e2f25100?source=rss-362236f45b44------2</link>
            <guid isPermaLink="false">https://medium.com/p/9139e2f25100</guid>
            <dc:creator><![CDATA[Koleti Swathi]]></dc:creator>
            <pubDate>Sat, 05 Apr 2025 16:31:28 GMT</pubDate>
            <atom:updated>2025-04-05T16:31:28.763Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Understanding AVERAGE, AVERAGEX, and AVERAGEA in Power BI: Differences, Use Cases &amp; Real-Time Implementation</strong></h3><p>In this article, I will discuss:</p><ul><li>AVERAGE()</li><li>AVERAGEX()</li><li>AVERAGEA()</li></ul><p>along with their underlying behavior and use cases. We’ll break each one down using real-world scenarios and demonstrate how to implement them effectively..</p><p>I have used three sample datasets to implants the averages,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/518/1*yAg0afYtBSNCIQ7qcuNcWA.png" /><figcaption>Sales</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/276/1*CFtHDSrTqvUdGnL8u3zTSg.png" /><figcaption>Customer</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/262/1*FoPiMeyc5wbWRnQHfM4sTQ.png" /><figcaption>Feedback</figcaption></figure><ol><li><strong>AVERAGE():</strong></li></ol><p><strong>Use AVERAGE() for quick aggregations</strong> when there’s no row-level logic involved and No need to iterate or apply filters.</p><h4>Real-Time Example:</h4><p><strong>Sales Dashboard</strong>: You want to display the average order amount from the Sales[OrderAmount] column.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/437/1*JjEywD8JWQhTXsBb9-lxRA.png" /><figcaption>AVERAGE()</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/313/1*BeQHTHtJ_xn5G9v2QMpZCQ.png" /></figure><h4><strong>AVERAGE() Behavior in DAX</strong></h4><ol><li>Blanks (Null Values)</li></ol><ul><li>Ignores completely: Excluded from calculation</li><li>Example: AVERAGE ([10, 20, BLANK(), 30]) = 20<br> (Calculates as (10+20+30)/3)</li></ul><p>2. Zeros (0 Values)</p><ul><li>Includes in calculation: Treated as valid numeric values</li><li>Example: AVERAGE([10, 0, 30]) = 13.33<br> (Calculates as (10+0+30)/3)</li></ul><p>3. Non-Numeric Values</p><ul><li>Returns error: Text/logical values cause failures</li></ul><h3>2.AVERAGEX():</h3><p><strong>AVERAGEX()</strong> is an <strong>iterator function</strong> in DAX. It <strong>evaluates an expression row by row</strong> over a specified <strong>table</strong> (or table expression), then <strong>returns the average</strong> of those values.</p><p>This function is particularly useful when:</p><ul><li>You need to <strong>perform calculations on each row</strong> before averaging.</li><li>You want to average <strong>custom expressions</strong>, not just a single column.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/512/1*V0HC2Ti4PNSY7niCvCH3ZQ.png" /><figcaption>Sales Table</figcaption></figure><p>we assume scenario like, we want to see that which customer from west region and ordered greater than equal to 300,To achieve this we use Averagex()</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/929/1*c8yQEvFnDHAHCwXSydZtXw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/399/1*WWC2IJpRjRWgB-IFNe3Azw.png" /></figure><p>In some scenarios we can handle text data types with AVERAGEX() function let’s assume some sample table and see how it works…</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/263/1*we-HDIBQUJpM6GnOsn3PVg.png" /><figcaption>Feedback Table</figcaption></figure><p>Here we want to find the average feedback for text column directly we can&#39;t do but we achieve through the explicit conversion using Measure.</p><p><strong>Goal</strong>: Calculate the average “score” from the Feedback Text column, where:</p><ul><li>&quot;Yes&quot;/&quot;TRUE&quot; = 1</li><li>&quot;No&quot;/&quot;FALSE&quot; = 0</li><li>Numeric strings (&quot;5&quot;, &quot;3&quot;, &quot;1&quot;) = their numeric value</li><li>&quot;Maybe&quot; = the text considered as 0</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/629/1*pRjc0KkBOqK56fVZB_xSyw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/274/1*iaVI8uyI5f3I_kiyoIVD8w.png" /></figure><h3>3.AVERAGEA():</h3><p>AVERAGEA function returns the arithmetic mean of all values in a column, including logical values (TRUE/FALSE).</p><h4>How AVERAGEA Works</h4><p>Unlike the standard AVERAGE function, which only considers <strong>numeric values</strong>, AVERAGEA includes:</p><ul><li>Numbers → counted</li><li><strong>TRUE</strong> → treated as 1</li><li><strong>FALSE</strong> → treated as 0</li><li><strong>Text and blanks</strong>:</li><li>Text is treated as 0</li><li>Blanks are ignored</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/267/1*30gUtFBrXUu8QR2D6Zu-zA.png" /><figcaption>Feedback Table</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/404/1*6zEYVVNqbI1JWzBHhFQw5g.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/237/1*uEA42o0AH58N7-S7vlAE_A.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/652/1*FnQb_-0vWnMgm-ttERISAw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/773/1*JeGDZ-tTjaMEhBHWU3Ms7A.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9139e2f25100" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Everything You Need to Know About Year-over-Year (YoY) Growth Beyond 100%]]></title>
            <link>https://medium.com/@koleti.swathi/everything-you-need-to-know-about-year-over-year-yoy-growth-beyond-100-1f16d7d9bd11?source=rss-362236f45b44------2</link>
            <guid isPermaLink="false">https://medium.com/p/1f16d7d9bd11</guid>
            <dc:creator><![CDATA[Koleti Swathi]]></dc:creator>
            <pubDate>Wed, 05 Mar 2025 13:53:04 GMT</pubDate>
            <atom:updated>2025-03-05T13:53:04.932Z</atom:updated>
            <content:encoded><![CDATA[<p>In this article, I will discuss YoY growth, where it applies, and how to analyze scenario when YoY growth exceeds the 100%, then we will see how companies use YoY to track their revenue and profits growth, using Box Office Data as an example.</p><p>Let’s start Understanding YoY Growth with Box Office Data</p><p>Indian film Industry wants to evaluate the growth of total worldwide collection and analyzing how the covid-19 pandemic effects the box office revenue and how the industry rebound the following years.</p><p>just assume that we have box office table that contains the following columns</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/365/1*J5NY1GWB2Bj_P37yRSTy2A.png" /><figcaption>Box-office Data</figcaption></figure><p>Based on table above, to determine YoY% growth. we need to compare each year total worldwide collection with the previous year’s total. The formula for YoY% growth is:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/603/1*O6UzhO4JdyOPkNEzG4iGyA.png" /></figure><p>Before going to achieve this in SQL , let’s see our table data first.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/654/1*K8hAEXam9O-eqf3c-7BhBg.png" /></figure><p>In the above table we have many columns but we only require the worldwide_colletion_in_crore column and year to see the growth of Indian movies in year wise.</p><p>Title — represents the movie name</p><p>Year — represents the release year of the movies</p><p>Worldwide_colletion_in_crore — the total worldwide earnings of the movie</p><p>Since the worldwide collection is listed at the individual movie level, we first need to calculate the worldwide collection per year using SQL aggregation functions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/722/1*W4bpnnNXU0p5vNHgm-YIYA.png" /></figure><p>Once aggregated, the data will look like this…</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/283/1*gOcxAIIqgK1ZmrSB59PN0A.png" /></figure><p>Here we have current year total collection in year wise but we need to know the previous year’s total collation dynamically for that we use LAG() windows function.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/764/1*zsAO247AduhzjZnK8-INLw.png" /></figure><p>Here, we use CTE table and LAG () function to calculate previous year total worldwide collection, the result as follows..</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/519/1*T90ngaccB_nuSFS_rR4c9A.png" /></figure><p>Now, we have current year collection and the previous year ‘s collection. These two columns help to find the YoY growth. Since we already know the formula, let’s implement it here …</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/962/1*GAsBMHDA7lh-ZHqH_qgNEg.png" /><figcaption>calculating YoY growth in SQL with the help of CTE tables and windows function</figcaption></figure><p>after implementing YoY% logic , our final data will look like…</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/521/1*3XL8L1w6g3kmkSxu6eVv_A.png" /></figure><p>Here are some key aspects we need to analyze….</p><p>1) Strong growth from 2015 to 2017</p><p>Industry experienced steady growth during year 2015–2017 and the biggest growth was Bahubali 2, which was collected 1800cr, making it one of the highest grossing Indian films all the time.</p><p>2) Negative growth in 2018 (-14%)</p><p>2018 saw a declined because 2017 was strong year due to Bahubali 2 movie and no other movie reached the same level.</p><p>3) No growth in 2019(0% growth)</p><p>The box office collection remained the same as 2018 due to covid-19(which started effecting 2020)</p><p>Passible reasons: No mega blockbuster movies in industry to gain revenue and increasing competition from streaming platforms (Netflix, Amazon Prime, Hotstar etc.…)</p><p>4) Covid-19 impact in 2020 (-72% declined) and partial recovery in 2021 (15%)</p><p>Covid_19 effect the year 2020 and 2021 got recovered little bit but far below the pre-pandemic level.</p><p>5) Exceptional growth in 2022 (263%)</p><p>2022 had extradinary growth because after pandemic audiences returning to theaters and had more big hits (RRR, Brahmastra, KGF2)</p><p>6) Continuous growth in 2023<strong> </strong>(37%) and declined in 2024 (-40%)</p><p>OTT streaming domination and no such big project movies were release or weak content could be affecting theatre revenue in 2024.</p><p><strong>How To Understand YoY Growth When It Exceeds 100%</strong></p><p>when YoY growth is over 100%, it means the revenue more than doubled compared to the previous year. But does that always mean real growth?</p><p>Let’s break it down….</p><p>Was the previous year unusually low?</p><p>Yes right, due to the pandemic (2020) had a big drop, If the previous year had a big drop doesn’t mean massive success — it could be bouncing back to normal.</p><p>Is the industry recovering?</p><p>A big jump might show recovery rather than actual new growth. Ex: in 2022(263%) , the film industry bounced back after covid-19</p><p>Is the growth long-lasting?</p><p>Huge hits like Baahubali 2 or RRR can cause sudden spikes, but its rare to maintain such high growth every year.</p><p>My Final Thoughts</p><p>YoY growth is important, but behind the things matters, the 263% rise in 2022 was mainly recovery, not long-term growth. The 40% drop in 2024 shows that industry need some strategies. Understand these trends helps business and industries plan for the future.</p><p>Stay tuned! I will explore these trends through visualizations in Power BI in my next blog…..</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1f16d7d9bd11" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>