<?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 Thebitoolbox on Medium]]></title>
        <description><![CDATA[Stories by Thebitoolbox on Medium]]></description>
        <link>https://medium.com/@thebitoolbox?source=rss-dd2443488cef------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*ya4GioS7GrRzdQY7vNFLJQ.png</url>
            <title>Stories by Thebitoolbox on Medium</title>
            <link>https://medium.com/@thebitoolbox?source=rss-dd2443488cef------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 18:31:38 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@thebitoolbox/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 to Find and Remove Unused Measures in Power BI (and Undo It)]]></title>
            <link>https://levelup.gitconnected.com/how-to-find-and-remove-unused-measures-in-power-bi-and-undo-it-ea9317330d97?source=rss-dd2443488cef------2</link>
            <guid isPermaLink="false">https://medium.com/p/ea9317330d97</guid>
            <category><![CDATA[tutorial]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[dax]]></category>
            <dc:creator><![CDATA[Thebitoolbox]]></dc:creator>
            <pubDate>Mon, 16 Nov 2026 15:42:52 GMT</pubDate>
            <atom:updated>2026-11-16T15:42:52.613Z</atom:updated>
            <content:encoded><![CDATA[<h4><em>Choose one unused measure, inspect the TMDL change, and verify that its similarly named neighbor survives.</em></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TnET7Jpjd3QeE03mQR5xew.png" /><figcaption><em>Cover illustration created with AI for theBItoolbox.</em></figcaption></figure><p>Two measures appear when I search for “date_me”: <strong>Date_measure</strong> and <strong>Date_measure_calc</strong>. I want to remove the first one and keep the second. The useful test is whether I can make that exact change, confirm the report still works, and bring the removed measure back.</p><p><em>Written with AI assistance from my demonstration scripts and test notes.</em></p><p>This walkthrough uses <strong>Model Cleaner</strong>, the free theBItoolbox template I built for reviewing unused measures and columns in saved Power BI projects. The example uses Studio, a small model with fictional data. It keeps the evidence visible without exposing a company model.</p><h3>Understand what the two measures do</h3><p>Date_measure returns a fixed date:</p><p>Date_measure = DATE(2026, 1, 1)</p><p>Date_measure_calc returns the latest date from a column:</p><p>Date_measure_calc = MAX(‘Season Item’[Snapshot Date])</p><p>The Studio report requests Date_measure_calc. It does not request Date_measure, and the supplied example has no dependency path that retains that fixed-date measure.</p><p>Their similar names do not establish a dependency. The definitions and report references do. In this example, the expected outcome is one removable measure and one retained measure.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CYRJbhwiNvijY2LEF_eLKA.png" /><figcaption><em>The two measures have different roles in Studio. Illustration: theBItoolbox.</em></figcaption></figure><h3>Prepare the saved target</h3><p>I keep a working copy of Studio and save it before scanning. Cleaner reads saved project files, so a measure created or removed in an unsaved Desktop session would not yet be reflected in the scan.</p><p>I open the Cleaner PBIT in a separate Desktop window and set <strong>ModelPath</strong> to Studio’s <strong>.SemanticModel</strong> folder. After refresh, <strong>Scope &amp; checks</strong> should list the intended local report and show complete scan coverage.</p><p>If coverage is incomplete, I investigate before preparing a cleanup batch. If the expected report is missing, I correct the scope first. A field can look unreferenced when the report that uses it was never scanned.</p><p>This workflow uses PBIP with TMDL model definitions. Microsoft’s <a href="https://learn.microsoft.com/en-us/power-bi/developer/projects/projects-overview">PBIP overview</a> explains that project structure. Model Cleaner does not scan a PBIX file directly.</p><h3>Search first and select second</h3><p>On <strong>Select &amp; clean</strong>, I set Show to All fields and search for “date_me”. Cleaner shows Date_measure as available and Date_measure_calc as locked by its report reference.</p><p>Even with two rows visible, the footer still shows <strong>zero selected</strong>. I tick only Date_measure, then confirm that the count becomes one. Filtering narrows the list; it does not select the displayed rows.</p><p>I leave <strong>Allow changes to Q&amp;A metadata</strong> off for this example. The selected measure does not require that permission. Changing an unrelated option would make the demonstration harder to interpret.</p><h3>Save the matching Restore</h3><p>I choose <strong>Review scripts</strong> and save <strong>Restore first</strong>, using a filename that identifies the model and this selection. I then copy or save Cleanup from the same batch.</p><p>Both script tabs provide <strong>Copy full script</strong> and <strong>Save .txt</strong>. These provide the complete generated script, so the workflow does not depend on copying a long report text cell. If the host disables file saving, I use Copy full script and paste into a text editor.</p><p>I keep the two files together. A Restore from an earlier selection is not automatically the matching undo for a later batch. If I change the selection, I save its new Restore before using its Cleanup.</p><h3>Inspect the difference in the target model</h3><p>In the working copy of Studio, I open <strong>TMDL view</strong>, paste Cleanup, and choose <strong>Preview</strong>. Microsoft’s <a href="https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-tmdl-view">TMDL view documentation</a> describes the preview and apply workflow.</p><p>The generated script can include a substantial table definition even when I selected only one measure. I check the actual difference: the Date_measure declaration should be removed, and Date_measure_calc should remain.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3sGgjp8aLWVDO9wWf_pvLg.png" /><figcaption><em>The same selection must supply both scripts. Workflow illustration: theBItoolbox.</em></figcaption></figure><p>Only after checking that difference do I apply the change. This is also a good moment to confirm that I am in the target project’s window, rather than the Cleaner window.</p><h3>Check the field list and the report</h3><p>After Cleanup, I search for “date_me” in the target model again. Date_measure has disappeared. Date_measure_calc remains.</p><p>The report still shows its control results: <strong>Margin KPI = 90</strong> and a latest date of <strong>8 January 2026</strong>. The field list confirms which object was removed; the report confirms that the displayed results in this example remain intact.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3LmiroNp_ZkAgl86olLvGg.png" /><figcaption><em>The expected sequence in the Studio demonstration. Illustration based on the confirmed removal and restoration; theBItoolbox.</em></figcaption></figure><p>On a production model, I would choose checks relevant to its business logic and review consumers outside the scanned files. One unchanged page does not establish that every external workbook or report is unaffected.</p><h3>Restore the same batch</h3><p>To reverse the demonstration, I paste the saved Restore into the target’s TMDL view, preview it, and apply it. Searching again shows both measures. The visible report values remain the same.</p><p>Restore reinstates the affected table and culture definitions captured at scan time. It is not a whole-project backup. Later edits to those same definitions could be overwritten by an older Restore, which is why I keep a backup and use the pair from the same selection.</p><p>After changing the target, I save it and refresh Cleaner before generating another batch. Otherwise the next plan could be based on the earlier saved state.</p><p>The demonstration is deliberately easy to audit: two similarly named measures, one checkbox, one removal, and a matching restoration. I can apply the same review process to a larger shortlist without treating every available field as a deletion recommendation.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FX3tuD41NzhI%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DX3tuD41NzhI&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FX3tuD41NzhI%2Fhqdefault.jpg&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/fd45d6d57860473d2538ab8b64fd19e4/href">https://medium.com/media/fd45d6d57860473d2538ab8b64fd19e4/href</a></iframe><p><strong>Download Model Cleaner free:</strong> <a href="https://thebitoolbox.gumroad.com/l/cleaner">https://thebitoolbox.gumroad.com/l/cleaner</a></p><p>More Power BI tools and tutorials: <a href="https://thebitoolbox.com/">theBItoolbox</a>.</p><p><em>I created Model Cleaner. This walkthrough describes version 0.1.12.1 and the supplied Studio example.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ea9317330d97" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/how-to-find-and-remove-unused-measures-in-power-bi-and-undo-it-ea9317330d97">How to Find and Remove Unused Measures in Power BI (and Undo It)</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Your Power BI Star Schema Isn’t a Star: Facts, Dimensions, Grain, and Relationships]]></title>
            <link>https://medium.com/@thebitoolbox/your-power-bi-star-schema-isnt-a-star-facts-dimensions-grain-and-relationships-4e45312ac83f?source=rss-dd2443488cef------2</link>
            <guid isPermaLink="false">https://medium.com/p/4e45312ac83f</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[dax]]></category>
            <dc:creator><![CDATA[Thebitoolbox]]></dc:creator>
            <pubDate>Fri, 06 Nov 2026 08:16:21 GMT</pubDate>
            <atom:updated>2026-11-06T08:16:21.880Z</atom:updated>
            <content:encoded><![CDATA[<h4>A Power BI model can look organized and still be poorly designed.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cck_fDk_GF1CkpzMmkgu3g.png" /><figcaption>Generated with ChatGPT</figcaption></figure><p>You can place FactSales in the middle.</p><p>Put DimDate, DimProduct, and DimCustomer around it.</p><p>Draw a few relationships.</p><p>It looks like a star.</p><p>But that does not automatically make it a good star schema.</p><p>The real question is not:</p><blockquote><em>Does the diagram look like a star?</em></blockquote><p>It is:</p><blockquote><strong><em>Do the tables have clear roles, consistent grain, and predictable filter paths?</em></strong></blockquote><p>Microsoft recommends star schema principles for Power BI semantic models because the pattern aligns naturally with how report queries work: dimensions support filtering and grouping, while fact tables support summarization.</p><p>That distinction is the foundation of this article.</p><h3>A star schema is not a drawing</h3><p>Consider this model:</p><pre>                DimDate<br>                   |<br>                   |<br>DimProduct ---- FactSales ---- DimCustomer<br>                   |<br>                   |<br>                DimStore</pre><p>At first glance, it looks exactly like what we expect.</p><p>But now imagine zooming out.</p><p>The real model is:</p><pre>DimCategory<br>      |<br>DimSubcategory<br>      |<br>DimProduct<br>      |<br>   FactSales<br>      |<br>DimCustomer<br>      |<br>   DimCity<br>      |<br> DimCountry</pre><p>There is nothing inherently invalid about this structure.</p><p>The model can work.</p><p>Filters can propagate.</p><p>Measures can calculate correctly.</p><p>But now the Product business entity is spread across:</p><ul><li>Product</li><li>Subcategory</li><li>Category</li></ul><p>And Customer is spread across:</p><ul><li>Customer</li><li>City</li><li>Country</li></ul><p>That is no longer a simple star. It is a <strong>snowflaked dimension structure</strong>.</p><p>Microsoft defines a snowflake dimension as a set of normalized tables representing a single business entity — for example Product, Subcategory, and Category stored separately.</p><p>The important point is that:</p><blockquote><strong><em>Snowflake does not automatically mean bad model.</em></strong></blockquote><p>The question is whether that complexity is intentional and useful.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DmzFjyJagAKQ9QRbrRaMRw.png" /><figcaption>FactSales is in the middle, but Product and Customer are still spread across chained dimensions.</figcaption></figure><h3>Why Power BI models often become snowflakes</h3><p>This frequently happens because developers reproduce the source-system structure directly inside Power BI.</p><p>Imagine the warehouse contains:</p><pre>Product<br>Subcategory<br>Category</pre><p>with relationships:</p><pre>Category<br>   ↓<br>Subcategory<br>   ↓<br>Product</pre><p>That structure can make perfect sense in a normalized relational database.</p><p>Normalization reduces repetition and provides a structured way to maintain related entities.</p><p>But a reporting semantic model has a different job.</p><p>Its job is to make filtering, grouping, summarization, and exploration easy for report authors and users.</p><p>Microsoft’s Power BI guidance explicitly distinguishes dimension tables — which describe business entities and support filtering/grouping — from fact tables, which store events and numeric values that are summarized.</p><p>So one useful principle is:</p><blockquote><strong><em>SOURCE MODEL ≠ REPORTING MODEL</em></strong></blockquote><p>Do not automatically copy the architecture of the source into the semantic model.</p><h3>Example: Product as one reporting dimension</h3><p>Suppose the source contains:</p><h3>DimProduct</h3><pre>ProductID<br>ProductName<br>SubcategoryID</pre><h3>DimSubcategory</h3><pre>SubcategoryID<br>Subcategory<br>CategoryID</pre><h3>DimCategory</h3><pre>CategoryID<br>Category</pre><p>The model becomes:</p><pre>DimCategory<br>     ↓<br>DimSubcategory<br>     ↓<br>DimProduct<br>     ↓<br>FactSales</pre><p>For reporting, however, users usually think about this as one concept:</p><blockquote><em>Product.</em></blockquote><p>They want to analyze:</p><ul><li>Product</li><li>Subcategory</li><li>Category</li></ul><p>They generally do not care that those attributes happen to live in three separate source tables.</p><p>For this model, we can expose them as:</p><pre>DimProduct</pre><pre>ProductID<br>ProductName<br>Subcategory<br>Category</pre><p>The relationship becomes:</p><pre>DimProduct<br>     ↓<br>FactSales</pre><p>Same business attributes.</p><p>Different model structure.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8CfkT7dDlb458aR-v5weDA.png" /><figcaption>This structure can make sense in the source system, but the reporting model has a different job.</figcaption></figure><h3>Where should you perform the flattening?</h3><p>In a production architecture, transformations like this should usually happen <strong>as close to the source as practical</strong>.</p><p>That might mean:</p><ul><li>data warehouse,</li><li>lakehouse,</li><li>SQL view,</li><li>ETL/ELT layer,</li><li>dataflow,</li><li>Power Query.</li></ul><p>Microsoft notes that when source preparation becomes large or complex, developing a data warehouse and ETL process before connecting the semantic model can be preferable.</p><p>For a small demonstration, though, Power Query Merge is an easy way to show the idea.</p><p>For example:</p><pre>DimProduct<br>    +<br>DimSubcategory<br>    +<br>DimCategory</pre><p>becomes:</p><pre>DimProduct_Flat</pre><p>with:</p><pre>ProductID<br>ProductName<br>Subcategory<br>Category</pre><p>The point is not:</p><blockquote><em>Always Merge tables in Power Query.</em></blockquote><p>The point is:</p><blockquote><strong><em>Expose business entities in a structure that makes sense for reporting.</em></strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/679/1*jUGQ-eRWOjD0c8QjVwvLSg.png" /><figcaption>The same reporting attributes are now exposed through one clear Product dimension.</figcaption></figure><h3>Customer can follow the same pattern</h3><p>Suppose we have:</p><pre>DimCountry<br>    ↓<br>DimCity<br>    ↓<br>DimCustomer<br>    ↓<br>FactSales</pre><p>Again, the source structure might be reasonable.</p><p>But for the reporting model we can potentially expose:</p><pre>DimCustomer</pre><pre>CustomerID<br>CustomerName<br>City<br>Country</pre><p>Then:</p><pre>DimCustomer<br>     ↓<br>FactSales</pre><p>The result becomes much easier to understand:</p><pre>                 DimDate<br>                    |<br>                    v</pre><pre>DimProduct ---- FactSales ---- DimCustomer<br>                    ^<br>                    |<br>                 DimStore</pre><p>Instead of asking users to understand several chains of related descriptive tables, we give them clear reporting dimensions.</p><h3>Before and after</h3><p>The original demo model might contain nine tables:</p><pre>DimCategory<br>DimSubcategory<br>DimProduct<br>DimCountry<br>DimCity<br>DimCustomer<br>DimDate<br>DimStore<br>FactSales</pre><p>After flattening the Product and Customer reporting dimensions:</p><pre>DimProduct<br>DimCustomer<br>DimDate<br>DimStore<br>FactSales</pre><p>That gives us:</p><pre>9 TABLES<br>   ↓<br>5 TABLES</pre><p>But table count alone is not the goal.</p><p>A model with fewer tables is not automatically better.</p><p>The improvement is:</p><pre>CHAINED DIMENSIONS<br>        ↓<br>DIRECT REPORTING DIMENSIONS</pre><p>and:</p><pre>MORE STRUCTURE<br>      ↓<br>CLEARER TABLE ROLES</pre><p>That is the real benefit.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YHMqJOl34SRmKkNM6GXYXw.png" /><figcaption>Same business information. Fewer relationship chains and clearer table roles.</figcaption></figure><h3>Snowflake is not an automatic mistake</h3><p>This deserves emphasis because “always flatten everything” would be just another bad rule.</p><p>There can be valid reasons to keep a snowflake structure.</p><p>For example:</p><h3>1. Shared dimension structures</h3><p>A hierarchy or business entity might genuinely be reused across multiple parts of an enterprise model.</p><h3>2. Very large dimensions</h3><p>There can be architectural or operational reasons not to duplicate a large set of descriptive attributes.</p><h3>3. Intentional enterprise architecture</h3><p>A governed semantic layer may deliberately preserve specific dimensional structures.</p><p>Microsoft itself notes that optimal model design is partly science and partly art, and that good guidance can sometimes be broken when there is a sound reason.</p><p>So:</p><pre>SNOWFLAKE<br>≠<br>AUTOMATIC MISTAKE</pre><p>A better test is:</p><pre>INTENTIONAL?<br>    → FINE</pre><pre>ACCIDENTAL?<br>    → REVIEW</pre><p>The mistake is not having multiple dimension tables.</p><p>The mistake is inheriting unnecessary complexity without understanding why it exists.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*90SJbIEJIcK-8QJvDiICGw.png" /><figcaption>The question is not whether the model contains a snowflake. The question is whether that structure is intentional</figcaption></figure><h3>Grain matters more than the diagram</h3><p>There is an even more important test.</p><p>Pick your fact table.</p><p>Then finish this sentence:</p><blockquote><strong><em>One row in this fact table represents…</em></strong></blockquote><p>For a sales fact table, the answer might be:</p><blockquote><em>One sales transaction line.</em></blockquote><p>So every row should represent the same level of detail.</p><p>Example:</p><pre>SalesLine<br>SalesLine<br>SalesLine<br>SalesLine</pre><p>Clear.</p><p>Now imagine this:</p><pre>SalesLine<br>MonthlyTarget<br>SalesLine<br>MonthlyTarget</pre><p>What does one row represent now?</p><p>There is no single answer.</p><p>You have mixed multiple grains inside the same structure.</p><p>Microsoft’s star-schema guidance specifically says fact tables should load data at a <strong>consistent grain</strong>, and explains that dimension-key values determine the granularity of a fact table.</p><p>This is why:</p><pre>ONE ROW = ONE SALES LINE</pre><p>is not just a nice slogan.</p><p>It is a fundamental modeling contract.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*P_JmygZIqyF7Cur5CfYVIg.png" /><figcaption>If you cannot describe the fact-table grain in one sentence, the problem is deeper than the shape of the model</figcaption></figure><h3>Why inconsistent grain becomes dangerous</h3><p>Suppose:</p><pre>Row 1 → Sales line → $200<br>Row 2 → Monthly target → $50,000<br>Row 3 → Sales line → $150</pre><p>Now someone writes:</p><pre>Total Amount :=<br>SUM ( FactMixed[Amount] )</pre><p>What exactly are they summing?</p><p>Sales?</p><p>Targets?</p><p>Both?</p><p>Suddenly every measure needs additional logic to distinguish different types of rows.</p><p>You can still make it work.</p><p>But you are making the semantic model responsible for untangling a structural problem.</p><p>That is a warning sign.</p><h3>Fact-to-fact relationships deserve scrutiny</h3><p>Imagine your model contains:</p><pre>FactSales</pre><p>and:</p><pre>FactReturns</pre><p>A tempting shortcut is:</p><pre>FactSales * ───── * FactReturns</pre><p>using ProductID, OrderID, or another field that contains duplicates on both sides.</p><p>Power BI supports many-to-many relationships.</p><p>That does not mean they should be your default modeling choice.</p><p>Microsoft’s guidance says direct many-to-many relationships between fact tables can be useful for quick exploration, but generally recommends avoiding them in favor of star-schema designs using dimension tables connected with one-to-many relationships.</p><p>Instead of:</p><pre>FactSales<br>    ↕<br>FactReturns</pre><p>consider:</p><pre>DimProduct<br>             /        \<br>            ↓          ↓<br>       FactSales    FactReturns</pre><p>and perhaps:</p><pre>DimCustomer<br>             /         \<br>            ↓           ↓<br>       FactSales     FactReturns</pre><p>Now Product filters both fact tables.</p><p>Customer filters both fact tables.</p><p>The facts do not need to filter each other.</p><p>Microsoft highlights exactly this advantage: shared dimensions allow report visuals to filter or group by dimension attributes while summarizing columns from related fact tables.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*P2WM-FSyCkwiwfIJRPNC2g.png" /><figcaption>A direct many-to-many relationship between facts should immediately trigger a modeling review. Often the cleaner pattern is to let shared dimensions filter both fact tables.</figcaption></figure><h3>Many-to-many is not automatically wrong either</h3><p>The lesson is not:</p><blockquote><em>Never use many-to-many.</em></blockquote><p>Power BI supports legitimate many-to-many scenarios.</p><p>For example:</p><pre>Customer<br>   ↓<br>Bridge<br>   ↑<br>Account</pre><p>A bridge table may be the correct way to represent a true many-to-many business relationship.</p><p>Microsoft explicitly documents this pattern and recommends a bridging table with one-to-many relationships when modeling many-to-many relationships between dimensions.</p><p>So again, the useful question is:</p><blockquote><strong><em>Why is this many-to-many relationship here?</em></strong></blockquote><p>Not:</p><blockquote><em>Is many-to-many always bad?</em></blockquote><h3>Technical keys are part of model design too</h3><p>A good semantic model is not only technically correct.</p><p>It should also be usable.</p><p>Consider:</p><pre>ProductID<br>CustomerID<br>SalesID<br>DateKey</pre><p>Those columns can be essential for relationships.</p><p>But should a report author see them in the Fields pane?</p><p>Usually not.</p><p>A report developer wants:</p><pre>Product<br>Category<br>Customer<br>Country<br>Date<br>Sales Amount<br>Profit</pre><p>not:</p><pre>ProductKey<br>CustomerKey<br>StoreKey<br>DateKey</pre><p>Microsoft’s many-to-many guidance similarly recommends hiding identifier columns that are not appropriate for reporting.</p><p>This is a small detail, but it changes the usability of a semantic model substantially.</p><p>Think of it as:</p><pre>NEEDED BY MODEL<br>≠<br>NEEDED BY USER</pre><h3>Five questions before calling your model a star schema</h3><p>Instead of judging the shape of your Model View, ask these five questions.</p><h3>1. Do tables have clear roles?</h3><p>Can you identify each table as primarily:</p><ul><li>dimension, or</li><li>fact?</li></ul><p>Microsoft recommends well-structured models where tables serve clear fact or dimension roles rather than mixing both.</p><h3>2. Does every fact table have a consistent grain?</h3><p>Can you finish:</p><blockquote><em>One row represents…</em></blockquote><p>with one clear answer?</p><p>If not, stop and investigate.</p><h3>3. Do dimensions usually filter facts directly?</h3><p>Not every indirect filter path is bad.</p><p>But if most business entities require navigating long relationship chains, ask whether those chains provide genuine value.</p><p>Power BI relationships establish the paths through which filters propagate between model tables, making those paths a fundamental part of semantic-model behavior.</p><h3>4. Are many-to-many relationships intentional?</h3><p>Every * : * relationship deserves an explanation.</p><p>Not necessarily removal.</p><p>An explanation.</p><p>What business relationship does it represent?</p><p>Why is a bridge not appropriate?</p><p>Why should these tables filter each other?</p><p>If nobody knows, review it.</p><h3>5. Could a new developer understand the model quickly?</h3><p>This is not a technical rule.</p><p>It is a practical test.</p><p>Imagine a new developer opens the model.</p><p>Can they quickly identify:</p><pre>FACTS<br>DIMENSIONS<br>FILTER PATHS<br>GRAIN</pre><p>Or do they need a twenty-minute explanation before changing anything?</p><p>Maintainability is part of model quality.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c1TQDCJyzPy6v70EBAygUA.png" /><figcaption>A better star-schema test than simply looking at the shape of Model view.</figcaption></figure><h3>Common misconceptions</h3><h3>“If Sales is in the middle, it’s a star schema.”</h3><p>No.</p><p>Diagram placement is cosmetic.</p><p>The model is defined by table roles, grain, relationships, and filter behavior.</p><h3>“Snowflake is always bad.”</h3><p>No.</p><p>Snowflake structures can be intentional and appropriate.</p><p>The question is whether the additional normalization adds useful architectural value.</p><h3>“Fewer tables always means a better model.”</h3><p>No.</p><p>Five poorly designed tables can be worse than ten well-designed ones.</p><p>The objective is:</p><blockquote><strong><em>the right number of tables with the right responsibilities.</em></strong></blockquote><h3>“Many-to-many means the model is wrong.”</h3><p>No.</p><p>Many-to-many relationships are valid for certain business scenarios.</p><p>But they should be deliberate.</p><h3>“I should flatten everything inside Power Query.”</h3><p>Not necessarily.</p><p>If you control the data platform, stable transformations might belong further upstream.</p><p>Power Query is one option, not automatically the architectural destination.</p><h3>“Performance is the only reason to use a star schema.”</h3><p>No.</p><p>Usability and maintainability matter too.</p><p>Microsoft explicitly positions star-schema design as relevant to Power BI models optimized for both <strong>performance and usability</strong>.</p><h3>Enterprise model review checklist</h3><p>Before approving a Power BI semantic model, review:</p><h3>Table roles</h3><ul><li>Is every major table clearly a fact or dimension?</li><li>Are descriptive attributes stored in logical reporting dimensions?</li><li>Are technical staging structures exposed unnecessarily?</li></ul><h3>Grain</h3><ul><li>Can every fact table’s grain be described in one sentence?</li><li>Are multiple grains mixed in one fact?</li><li>Do relationships match that grain?</li></ul><h3>Relationships</h3><ul><li>Are cardinalities correct?</li><li>Are filter directions intentional?</li><li>Are fact-to-fact relationships necessary?</li><li>Are many-to-many relationships justified?</li><li>Are there unnecessarily long filter paths?</li></ul><h3>Dimensions</h3><ul><li>Are Product attributes scattered across unnecessary tables?</li><li>Are Customer attributes fragmented?</li><li>Could normalized structures be exposed more simply to report authors?</li></ul><h3>Usability</h3><ul><li>Are surrogate and technical keys hidden?</li><li>Are field names understandable?</li><li>Can report authors easily find the business attributes they need?</li></ul><h3>Maintainability</h3><ul><li>Could another developer understand the architecture quickly?</li><li>Are unusual relationships documented?</li><li>Is the reason for every architectural exception clear?</li></ul><h3>The final rule</h3><p>A star schema is not a drawing.</p><p>It is a modeling pattern.</p><pre>CLEAR DIMENSIONS</pre><pre>CLEAR FACTS</pre><pre>CONSISTENT GRAIN</pre><pre>PREDICTABLE RELATIONSHIPS</pre><p>The goal is not to force every Power BI model into a perfect geometric star.</p><p>The goal is to build a semantic model whose structure matches the way users need to analyze the business.</p><p>So before you look at the diagram and say:</p><blockquote><em>“That looks like a star.”</em></blockquote><p>Ask:</p><blockquote><strong><em>Does it actually behave like one?</em></strong></blockquote><h3>Watch the full Power BI example</h3><p>In the video version of this article, I walk through the complete transformation:</p><ul><li>normalized Product and Customer dimensions,</li><li>snowflake filter paths,</li><li>flattening reporting dimensions,</li><li>before vs after model architecture,</li><li>fact-table grain,</li><li>fact-to-fact relationships,</li><li>many-to-many review,</li><li>and a five-question architecture checklist.</li></ul><iframe src="https://cdn.embedly.com/widgets/media.html?url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dg5zraqVuEoA&amp;type=text%2Fhtml&amp;schema=youtube&amp;display_name=YouTube&amp;src=https%3A%2F%2Fwww.youtube.com%2Fembed%2Fg5zraqVuEoA" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/e01db661507553692ea9870a58714005/href">https://medium.com/media/e01db661507553692ea9870a58714005/href</a></iframe><h3>Reviewing larger semantic models</h3><p>With a five-table demo model, relationship problems are obvious.</p><p>With dozens of tables, hundreds of measures, multiple relationship paths, and years of accumulated development, they are much harder to spot.</p><p>That is where an automated model audit can help.</p><p>ModelLens can surface model structure, relationships, filter directions, cardinalities, dependencies, and table metadata so you can identify areas that deserve review.</p><p>The goal is not:</p><blockquote><em>“Automatically convert every snowflake into a star.”</em></blockquote><p>The goal is:</p><blockquote><strong><em>See the architecture clearly enough to make an informed decision.</em></strong></blockquote><p><strong>ModelLens:</strong><br><a href="https://thebitoolbox.gumroad.com/l/modellens">https://thebitoolbox.gumroad.com/l/modellens</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4e45312ac83f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Power BI Format Strings Explained: Why Measure Formatting Belongs in the Model]]></title>
            <link>https://medium.com/@thebitoolbox/power-bi-format-strings-explained-why-measure-formatting-belongs-in-the-model-42a3b237982c?source=rss-dd2443488cef------2</link>
            <guid isPermaLink="false">https://medium.com/p/42a3b237982c</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <dc:creator><![CDATA[Thebitoolbox]]></dc:creator>
            <pubDate>Fri, 06 Nov 2026 08:16:14 GMT</pubDate>
            <atom:updated>2026-11-06T08:16:14.139Z</atom:updated>
            <content:encoded><![CDATA[<h4>A correct number can still communicate the wrong thing.</h4><p>Imagine you open a Power BI report and see this:</p><p><strong>0.32</strong></p><p>The calculation is correct.</p><p>No DAX error.</p><p>No broken relationship.</p><p>No missing data.</p><p>But what does 0.32 actually mean?</p><p>Is it:</p><ul><li>32% margin?</li><li>$0.32?</li><li>0.32 units?</li><li>a ratio?</li><li>a decimal that should stay exactly as it is?</li></ul><p>The value alone does not tell you.</p><p>Now change the display to:</p><p><strong>32.0%</strong></p><p>Nothing about the underlying calculation changed.</p><p>The number is still 0.32.</p><p>But suddenly the business meaning is obvious.</p><p>That is why formatting in Power BI is not merely decoration.</p><p>It is part of how a measure communicates its meaning.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kMszTxTPlDHSNvvNMwKOfw.png" /><figcaption>Generated with ChatGPT</figcaption></figure><h3>A correct value can still be unclear</h3><p>Consider a simple margin calculation:</p><pre>Margin % :=<br>DIVIDE (<br>    [Profit],<br>    [Sales Amount]<br>)</pre><p>Suppose it returns:</p><pre>0.32</pre><p>Mathematically, that is perfectly correct.</p><p>But a business user is unlikely to think about Margin as a decimal between zero and one.</p><p>They expect:</p><pre>32.0%</pre><p>The difference is presentation.</p><p>And that presentation carries meaning.</p><p>The important point is that Power BI does not need a different calculation to display the number as a percentage.</p><p>The measure can remain numeric.</p><p>You simply assign the appropriate format string.</p><p>That means:</p><pre>Underlying value: 0.32<br>Displayed value:  32.0%</pre><p>The number stays numeric.</p><p>Only the presentation changes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YFLa1prQROwAwSQSvB7wug.png" /></figure><h3>Format strings are model metadata</h3><p>A useful way to think about a format string is as <strong>metadata attached to a measure</strong>.</p><p>The DAX expression answers:</p><blockquote><em>What is the value?</em></blockquote><p>The format string answers:</p><blockquote><em>How should that value be communicated?</em></blockquote><p>For example:</p><pre>Sales Amount :=<br>SUM ( FactSales[SalesAmount] )</pre><p>The result might internally be:</p><pre>125430.75</pre><p>A business user probably wants:</p><pre>$125,430.75</pre><p>or perhaps:</p><pre>$125K</pre><p>depending on the report.</p><p>Similarly:</p><pre>Sales Quantity :=<br>SUM ( FactSales[Quantity] )</pre><p>might use:</p><pre>#,0</pre><p>while:</p><pre>Average Selling Price :=<br>DIVIDE (<br>    [Sales Amount],<br>    [Sales Quantity]<br>)</pre><p>might use:</p><pre>$#,0.00</pre><p>These measures are all numeric.</p><p>But they represent different concepts.</p><p>Their formatting should communicate that difference.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/1*IpQxGSMgGKTqWRlV-f_KSQ.png" /></figure><h3>Format once, reuse everywhere</h3><p>One of the advantages of formatting at the measure level is consistency.</p><p>Suppose [Sales Amount] is used in:</p><ul><li>a matrix,</li><li>a card,</li><li>a chart,</li><li>a tooltip,</li><li>another report page.</li></ul><p>If the measure itself has the correct model-level format, every visual starts with the same definition.</p><p>You do not need to remember:</p><blockquote><em>“This is Sales, so set currency here.”</em></blockquote><p>Then again on another visual.</p><p>And again on another page.</p><p>Visual-level formatting still has legitimate uses.</p><p>Sometimes one visual needs a different number of decimals or a different display unit.</p><p>But the model should provide a sensible default.</p><p>A measure with stable business meaning should usually also have stable formatting.</p><p>That reduces repetition and makes the semantic model easier for report authors to use.</p><h3>The FORMAT() trap</h3><p>There is another way to make 0.32 look like 32.0%.</p><p>You can write:</p><pre>Margin % Text :=<br>FORMAT (<br>    [Margin %],<br>    &quot;0.0%&quot;<br>)</pre><p>And visually, it works.</p><p>You get:</p><pre>32.0%</pre><p>So what is the problem?</p><p>FORMAT() returns <strong>text</strong>.</p><p>That is a very different thing from applying a format string to a numeric measure.</p><p>With:</p><pre>Margin % :=<br>DIVIDE (<br>    [Profit],<br>    [Sales Amount]<br>)</pre><p>the measure remains numeric.</p><p>With:</p><pre>Margin % Text :=<br>FORMAT (<br>    [Margin %],<br>    &quot;0.0%&quot;<br>)</pre><p>the result is now a string.</p><p>You have changed the data type of the output.</p><p>That can affect how the value behaves in charts, sorting, conditional formatting, calculations, aggregations, and other numeric scenarios.</p><p>So if your goal is simply:</p><blockquote><em>“I want this numeric measure to display as a percentage”</em></blockquote><p>FORMAT() is usually the wrong tool.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*92Rt6m8ZQiF1s-6Qz-WraQ.png" /></figure><h3>FORMAT() is not bad</h3><p>This distinction matters.</p><p>The rule is not:</p><blockquote><em>Never use </em><em>FORMAT().</em></blockquote><p>FORMAT() is useful when the result genuinely needs to be text.</p><p>For example, you may intentionally build a label:</p><pre>KPI Label :=<br>&quot;Margin: &quot;<br>    &amp; FORMAT ( [Margin %], &quot;0.0%&quot; )</pre><p>Now text is exactly what you want.</p><p>The problem is using FORMAT() only because a numeric measure needs a prettier display.</p><p>That solves a formatting problem by changing the value into a different data type.</p><p>A better question is:</p><blockquote><em>Do I need text, or do I need a numeric value with a format?</em></blockquote><p>Those are two different requirements.</p><h3>Static format strings work when meaning is stable</h3><p>Many measures represent one business concept.</p><p>For example:</p><pre>Sales Amount :=<br>SUM ( FactSales[SalesAmount] )</pre><p>That measure means Sales Amount regardless of where it appears.</p><p>Its format is stable.</p><p>Perhaps:</p><pre>$#,0.00</pre><p>Similarly:</p><pre>Margin % :=<br>DIVIDE (<br>    [Profit],<br>    [Sales Amount]<br>)</pre><p>always represents a ratio.</p><p>Its format may be:</p><pre>0.0%</pre><p>That is a perfect use case for a static format string.</p><p>The business meaning does not change.</p><p>So the format does not need to change either.</p><p>This sounds obvious until you build measures whose meaning <em>does</em> change dynamically.</p><h3>What happens when one measure represents several KPIs?</h3><p>Suppose you create a KPI selector:</p><pre>KPI Selector =<br>DATATABLE (<br>    &quot;KPI&quot;, STRING,<br>    {<br>        { &quot;Sales&quot; },<br>        { &quot;Margin %&quot; },<br>        { &quot;Units&quot; }<br>    }<br>)</pre><p>Then one dynamic measure:</p><pre>Selected KPI :=<br>SWITCH (<br>    SELECTEDVALUE ( &#39;KPI Selector&#39;[KPI] ),<br>    &quot;Sales&quot;, [Sales Amount],<br>    &quot;Margin %&quot;, [Margin %],<br>    &quot;Units&quot;, [Sales Quantity]<br>)</pre><p>Now the same measure can mean three very different things.</p><p>When the user selects Sales:</p><pre>125000</pre><p>should probably display as:</p><pre>$125,000</pre><p>When they select Margin:</p><pre>0.32</pre><p>should display as:</p><pre>32.0%</pre><p>When they select Units:</p><pre>8420</pre><p>should display as:</p><pre>8,420</pre><p>One static format string cannot correctly represent all three.</p><p>If you choose currency, Margin and Units look wrong.</p><p>If you choose percentage, Sales becomes nonsense.</p><p>If you leave everything as General, the business meaning becomes unclear.</p><p>This is where <strong>dynamic format strings</strong> become useful.</p><h3>Dynamic format strings preserve the numeric value</h3><p>A dynamic format expression can react to the same context as the measure.</p><p>For example:</p><pre>SWITCH (<br>    SELECTEDVALUE ( &#39;KPI Selector&#39;[KPI] ),<br>    &quot;Sales&quot;, &quot;$#,0&quot;,<br>    &quot;Margin %&quot;, &quot;0.0%&quot;,<br>    &quot;Units&quot;, &quot;#,0&quot;,<br>    &quot;#,0.00&quot;<br>)</pre><p>Now:</p><pre>Sales    → $125,000<br>Margin % → 32.0%<br>Units    → 8,420</pre><p>The important part is not only that the display changes.</p><p>The underlying measure <strong>remains numeric</strong>.</p><p>That is the key advantage over converting the result to text with FORMAT().</p><p>You get context-sensitive presentation without sacrificing numeric behavior.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_gdD8FK31wyrHZ0YQ6KZww.png" /></figure><h3>Dynamic does not mean better</h3><p>Once people discover dynamic format strings, it is tempting to make everything dynamic.</p><p>That is not the goal.</p><p>If [Sales Amount] is always Sales Amount, a static currency format is simpler and easier to understand.</p><p>There is no benefit in adding dynamic logic just because the feature exists.</p><p>Use dynamic formatting when the <strong>business meaning itself changes with context</strong>.</p><p>A useful principle is:</p><blockquote><em>Stable meaning → static format.</em></blockquote><blockquote><em>Dynamic meaning → dynamic format.</em></blockquote><p>This keeps the model simpler.</p><p>And simplicity matters.</p><p>Every additional dynamic expression becomes another piece of logic somebody may eventually need to understand.</p><h3>Formatting can also expose modeling mistakes</h3><p>Formatting is not only about aesthetics.</p><p>It can reveal semantic inconsistencies.</p><p>Imagine these measures:</p><pre>Revenue            125430<br>Margin             0.32<br>Customer Count     842<br>Average Price       14.9</pre><p>All four displayed as General.</p><p>Technically, nothing is broken.</p><p>But the model is pushing interpretation onto the report author.</p><p>The author must know:</p><ul><li>Revenue is currency.</li><li>Margin is a ratio.</li><li>Customer Count is an integer.</li><li>Average Price is currency with decimals.</li></ul><p>A semantic model should reduce that ambiguity.</p><p>Good formatting is part of creating a model that is easier to consume.</p><p>This matters even more when the model is reused by people who did not build it.</p><h3>Audit format strings instead of clicking every measure</h3><p>In a small model, checking formats manually is easy.</p><p>In a semantic model with hundreds of measures, it becomes a metadata problem.</p><p>Power BI can expose measure metadata directly.</p><p>For example:</p><pre>EVALUATE</pre><pre>SELECTCOLUMNS (<br>    INFO.VIEW.MEASURES(),<br>    &quot;Table&quot;, [Table],<br>    &quot;Measure&quot;, [Name],<br>    &quot;Format String&quot;, [FormatString],<br>    &quot;Dynamic Format&quot;, [FormatStringDefinition]<br>)</pre><p>Now you can review:</p><pre>Measure                 Format String     Dynamic Format<br>---------------------------------------------------------<br>Sales Amount             $#,0.00<br>Margin %                  0.0%<br>Sales Quantity            #,0<br>Selected KPI                               SWITCH(...)</pre><p>This gives you a model-level inventory.</p><p>You can quickly find:</p><ul><li>measures with no explicit format,</li><li>measures with dynamic formatting,</li><li>suspicious inconsistencies,</li><li>candidates for review.</li></ul><p>But again, metadata is a signal.</p><p>Not every measure with a blank format string is automatically wrong.</p><p>Context still matters.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/630/1*FBLZJ_5n3yk8rGoQ98uaMw.png" /></figure><h3>Four questions before formatting a measure</h3><p>Before choosing a format, I like to ask four questions.</p><h3>What does the value mean?</h3><p>Is it currency?</p><p>A percentage?</p><p>A count?</p><p>A date?</p><p>A ratio?</p><p>A price?</p><p>The format should follow the business meaning.</p><h3>Is that meaning static or dynamic?</h3><p>Does the measure always represent one concept?</p><p>Or can its meaning change depending on a selector or another context?</p><h3>Am I keeping the result numeric?</h3><p>If the output is conceptually numeric, avoid converting it to text simply for presentation.</p><h3>Will the formatting stay consistent across reports?</h3><p>If multiple report authors use the semantic model, the measure should provide a useful default rather than relying on every author to make the same formatting decision manually.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Kv5uiRQRzOkxyPeml_P5fQ.png" /></figure><h3>Where ModelLens fits</h3><p>Formatting is another example of a problem that becomes difficult only when the model grows.</p><p>With ten measures, you can inspect everything manually.</p><p>With hundreds of measures, you want to see the model as a system.</p><p>That is where metadata auditing becomes useful.</p><p>A tool such as ModelLens can help surface model context around measures, definitions, dependencies, and audit information so that suspicious areas are easier to review.</p><p>The goal should not be:</p><blockquote><em>“This format is automatically wrong.”</em></blockquote><p>It should be:</p><blockquote><em>“Here are the measures and their metadata. Which ones deserve attention?”</em></blockquote><p>That distinction is important throughout semantic-model auditing.</p><p><strong>Candidate ≠ error.</strong></p><p>Automation should help you find the questions.</p><p>It should not pretend every modeling decision has one universal answer.</p><p><a href="https://thebitoolbox.gumroad.com/l/modellens">ModelLens: Automated Power BI Semantic Model Auditor &amp; Documentation</a></p><h3>The final rule</h3><p>A format string should communicate the meaning of the measure without changing what the value actually is.</p><p>A useful framework is:</p><h3>Stable business meaning</h3><h3>STATIC FORMAT STRING</h3><h3>Meaning changes with context</h3><h3>DYNAMIC FORMAT STRING</h3><h3>Genuine text output</h3><h3>FORMAT() — INTENTIONALLY</h3><p>And above all:</p><h3>FORMAT IS PART OF THE MEASURE</h3><p>A measure is not complete just because the calculation returns the correct number.</p><p>The next question is:</p><blockquote><em>Will the person looking at that number understand what it means?</em></blockquote><p>0.32 may be correct.</p><p>32.0% tells the story.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rTKxtH8BTsl2ftcDF9HHlw.png" /></figure><h3>Watch the full walkthrough</h3><p>In <strong>Power BI Mistakes #6</strong>, I show why a correctly calculated measure can still communicate the wrong thing, when static and dynamic format strings make sense, and why FORMAT() is not a replacement for proper numeric formatting.</p><p><strong>▶ Watch Power BI Mistakes #6:</strong></p><iframe src="https://cdn.embedly.com/widgets/media.html?url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DGCwyiSd8Yik&amp;type=text%2Fhtml&amp;schema=youtube&amp;display_name=YouTube&amp;src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FGCwyiSd8Yik" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/2f8989a3e8ee8d932c1867b3015a3d22/href">https://medium.com/media/2f8989a3e8ee8d932c1867b3015a3d22/href</a></iframe><p><strong>🔍 ModelLens:</strong><br> <a href="https://thebitoolbox.gumroad.com/l/modellens">ModelLens: Automated Power BI Semantic Model Auditor &amp; Documentation</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=42a3b237982c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Audit a Power BI Semantic Model Using TMDL for AI]]></title>
            <link>https://medium.datadriveninvestor.com/how-to-audit-a-power-bi-semantic-model-using-tmdl-for-ai-4a8aec9bf616?source=rss-dd2443488cef------2</link>
            <guid isPermaLink="false">https://medium.com/p/4a8aec9bf616</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <category><![CDATA[large-language-models]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Thebitoolbox]]></dc:creator>
            <pubDate>Mon, 02 Nov 2026 02:46:52 GMT</pubDate>
            <atom:updated>2026-11-02T02:46:52.771Z</atom:updated>
            <content:encoded><![CDATA[<h4>A practical workflow for finding ambiguous metadata and testing the answers it supports</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dehXSQetGA7oddFU9oKgKg.png" /><figcaption>Power BI model auditing with TMDL for AI analytics. AI-generated illustration by theBItoolbox</figcaption></figure><p>A Power BI question can produce valid DAX and still return the wrong business answer. Ask for “sales last month,” and the result might use gross revenue instead of net revenue, shipment date instead of order date, or a calendar month instead of a fiscal period. The calculation can execute successfully while answering a different question from the one the user intended.</p><p><em>Disclosure: This article was drafted with AI assistance.</em></p><p>These are useful problems to investigate before expanding an AI analytics rollout. Some originate in the model itself. Others arise when an AI system chooses among several plausible interpretations of the model.</p><p>TMDL, the Tabular Model Definition Language, gives us a readable representation of a Power BI semantic model. We can inspect that representation for ambiguous names, missing definitions, questionable aggregation settings, and relationships that deserve review. We can also compare successive versions as the model changes. [1]</p><p>This article develops an audit workflow around a small sales example. The examples are illustrative, and the workflow does not require running Copilot. Testing actual AI answers comes afterward, in the environment where users will consume them.</p><p>The distinction matters: passing a metadata check establishes something about the model definition. It does not establish how often an AI system will answer business questions correctly.</p><h3>Start with the business question</h3><p>Before opening a model file, write down one question the model should answer and the interpretation its owner approves.</p><p>For our example, use this question:</p><p><em>What were net sales by product for the previous calendar month?</em></p><p>Assume the owner confirms that net sales means the amount after line discounts and before tax, expressed in USD, using order date. The example contains completed orders and no returns. A production model would need an explicit policy for returns, cancellations, currency conversion, and incomplete periods.</p><p>That definition gives the audit a purpose. We can investigate whether the model offers an appropriate measure, whether its date relationship matches the question, and whether the definitions distinguish it from other plausible calculations.</p><p>Without this reference, an audit can count populated Description properties while missing the ambiguity that matters most to the user.</p><p>Microsoft’s Copilot guidance similarly recommends defining the expected calculation, grouping, date interpretation, filters, and checked result for a focused set of business questions. It also emphasizes fixing modeling problems before relying on additional AI instructions. [2]</p><h3>Open the model definition</h3><p>Save a working copy of your model as a Power BI Project, or PBIP. Power BI stores the report and semantic model definitions in separate folders. You need the semantic model definition; a report that only connects to a remote model is not a substitute for that model’s metadata. [3]</p><p>For a project stored in TMDL format, begin in:</p><pre>SalesAudit.SemanticModel/definition/</pre><p>Open tables/Sales.tmdl to inspect the Sales table and its objects. Use relationships.tmdl to inspect relationships, and model.tmdl for model-level properties. If the project instead contains model.bim, its model definition is stored as TMSL rather than TMDL. [1, 4]</p><p>Record the model version and export date with your findings. An audit of yesterday’s files cannot establish what is currently deployed.</p><p>This workflow reads metadata. It does not need to refresh the model or execute its Power Query expressions. However, metadata can still contain source addresses, embedded literals, and business logic. Use an appropriate review environment when sharing the files.</p><p>If you automate extraction, use a TMDL-aware reader. Microsoft’s TmdlSerializer can deserialize a definition folder into the Tabular Object Model, or TOM. That provides an object structure on which to evaluate rules. [5]</p><p>Avoid building the audit around a search for isolated keywords. TMDL can contain multiline DAX and M expressions, quoted names, and fenced expressions. A token found inside an expression is not necessarily an object declaration. [1]</p><h3>Check descriptions against business meaning</h3><p>Consider this excerpt from Sales.tmdl. The snippets show selected objects and properties, rather than complete deployable model definitions:</p><pre>table Sales<br> <br> measure &#39;Net Sales&#39; = SUM(Sales[NetAmount])<br> formatString: #,##0.00</pre><p>The expression tells us which column is summed. It does not, by itself, establish what NetAmount includes, how currency was handled, or whether returned orders are present upstream.</p><p>A useful description should help answer those questions. In TMDL, an object’s Description is represented with /// immediately above its declaration. [1]</p><p>Here is a revised excerpt for our deliberately limited example:</p><pre>/// One row per completed order in this example.<br> /// Amounts are in USD. Returns are not included.<br> table Sales<br> <br> /// Sum of NetAmount after line discounts and before tax,<br> /// in USD, within the current filter context.<br> /// Standard calendar reporting uses order date.<br> measure &#39;Net Sales&#39; = SUM(Sales[NetAmount])<br> formatString: #,##0.00</pre><p>The description records an agreed definition. It does not make the expression enforce that definition. If a production source contains cancelled orders, writing “completed orders only” above an unrestricted SUM will not exclude them.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5AilFwPjcpvqEz7FS07uog.png" /><figcaption>Adding a Description records the agreed business meaning while leaving the DAX expression unchanged. Illustrative TMDL excerpts. Graphic by theBItoolbox, created with AI assistance.</figcaption></figure><p>Start with two straightforward checks: whether an in-scope object has a nonblank description, and whether its description merely repeats its name. The latter catches metadata such as a measure named Net Sales whose entire description is “Net Sales.”</p><p>Then review the business-critical definitions manually. Useful details include the unit, grain, exclusions, and circumstances in which the object should be used. An automated presence check cannot establish whether those details are true.</p><p>A separate data dictionary can support this work, but rows in a table called DataDictionary do not automatically populate the Description properties of the objects they describe. Keep the dictionary and actual model metadata synchronized if both are maintained.</p><p>Report description coverage separately for tables, columns, and measures. For example, three described measures out of five eligible measures means 60% coverage for that scope. State whether hidden objects are included, and use “not applicable” when the denominator is zero. Calling that number an AI readiness score would give it a meaning the calculation does not support.</p><h3>Inspect aggregation and field selection</h3><p>A second excerpt reveals a different kind of risk:</p><pre>table Sales<br> <br> column CustomerID<br> dataType: int64<br> sourceColumn: CustomerID<br> summarizeBy: sum</pre><p>The observable fact is that an integer column named CustomerID has Sum as its default aggregation. The interpretation is that this might be an identifier being treated as a quantity.</p><p>Keep those statements separate in the finding. A name ending in ID is a signal worth reviewing; it is not proof of the column’s business role.</p><p>Once the owner confirms that the column is an identifier, summarizeBy: none removes that default summation behavior. It does not prevent an explicitly written DAX expression from summing the column. Likewise, hiding a technical column can simplify field selection, but hiding is not an access-control mechanism.</p><p>For common business calculations, review the explicit measures available to consumers. An explicit measure holds a defined DAX calculation, while an implicit measure results from aggregating a column in a visual. Microsoft’s star-schema guidance explains that distinction. [6]</p><p>The presence of five explicit measures does not establish that every important business question has a suitable measure. Review the questions themselves. “How many customers?” could require a distinct count; “How many orders?” could require something different, depending on the table’s grain.</p><p>Also look for repeated names across tables, such as Status, Amount, and Date. Repetition is often legitimate. The useful check is whether names and surrounding context let a reviewer distinguish order status from delivery status, or order date from posting date.</p><p>Visible relationship keys deserve the same contextual review. In many models, a descriptive dimension attribute is the intended grouping field. An exposed key may be necessary for operational analysis, so record the reason before deciding to hide it.</p><h3>Review relationships and date roles</h3><p>Relationships determine how filters move through a model. Inspect their endpoints, cardinality, active status, and filter direction. [7]</p><p>For the example question, trace how the product filter and calendar filter reach Sales. Then check whether the relevant measure changes that behavior through its DAX expression.</p><p>A table can contain both OrderDate and ShipDate. An active relationship tells us which path is normally available, but does not establish which date the business intended in a natural-language request. Explicit measures or a documented clarification policy may be needed for the alternative date role.</p><p>Flag many-to-many relationships for review with their actual endpoints. Do not label every occurrence a modeling error. Likewise, an inactive relationship can serve an intentional calculation, and a disconnected table can provide input to a measure. Microsoft’s relationship documentation describes valid disconnected-table patterns. [7]</p><p>Review optional bidirectional filtering with particular care. Microsoft recommends limiting it, while documenting scenarios where it is needed. One-to-one relationships require bidirectional filtering, so a rule that simply counts every Both setting as a warning loses an important distinction. [8]</p><p>The output should explain which behavior needs checking. “Four many-to-many relationships” is an inventory metric. A finding should identify a relationship and the business calculation whose behavior requires review.</p><h3>Preserve uncertainty in the audit result</h3><p>An audit needs a way to say that the available evidence is insufficient. I use four outcomes for individual checks:</p><p>· <strong>WARNING:</strong> the rule detected a condition that needs review.</p><p>· <strong>PASS:</strong> the available metadata satisfied this particular check.</p><p>· <strong>NOT APPLICABLE:</strong> the object is outside the rule’s defined scope.</p><p>· <strong>NOT EVALUATED:</strong> the reader lacks evidence required to reach a verdict.</p><p>These are proposed audit outcomes, not Power BI certification statuses.</p><p>Consider a check that looks for several date fields without role descriptions. If some column types are unavailable, the reader may be unable to determine how many date fields the table contains. Returning PASS would conceal that uncertainty.</p><p>An omitted serialized property also needs careful handling. It might have a defined default, be inferred elsewhere, or be unsupported by the reader. Do not turn every absence into an error or invent a value merely to complete the evaluation.</p><p>For each finding, retain the object, rule version, observed evidence, suggested review, and limitations. For the CustomerID example, that might look like this:</p><pre>Object: Sales[CustomerID]<br> Check: Sum on an identifier candidate<br> Outcome: WARNING<br> Evidence: int64; summarizeBy = sum; name ends in ID<br> Review: Confirm its role and choose an appropriate aggregation<br> Limit: The name does not prove that this is an identifier</pre><p>This format lets another person challenge the interpretation without losing the observation that produced it.</p><h3>Audit the AI configuration separately</h3><p>Power BI’s Prep data for AI features add another layer of configuration to inspect: AI data schemas, AI instructions, and verified answers. Keep their availability and configuration separate from structural findings. [9]</p><p>An AI data schema defines which parts of a model Copilot prioritizes for supported tasks. AI instructions can clarify terminology and interpretation. Verified answers associate questions with responses prepared around reviewed visuals. Their presence establishes configuration, not demonstrated answer quality.</p><p>There is also an important feature-specific limitation. Microsoft’s current FAQ says descriptions affect DAX-query and search capabilities, while its capability matrix does not list descriptions as an input to the “Ask a question about my semantic model” experience. The same matrix shows different behavior for AI data schemas, instructions, and verified answers. [10]</p><p>Consequently, adding descriptions is useful metadata work, but you must test the exact Copilot experience your users rely on. For a custom LLM application, explicitly verify which metadata your retrieval and tool interfaces expose.</p><p>A local audit should report an AI artifact as present only when its reader can identify and interpret the relevant representation. Unsupported or unavailable configuration needs an explicit unknown result. The Approved for Copilot setting is managed in the Power BI service; do not infer its state from an unrelated local annotation. [9]</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WoWWLBGKGyEUpGK-SnjkQg.png" /><figcaption>An agreed business question guides the model audit, the review of available AI configuration and the evaluation of actual answers. Diagram by theBItoolbox, created with AI assistance.</figcaption></figure><h3>Turn findings into answer tests</h3><p>After correcting the model, rerun the same metadata checks. Then evaluate the business questions against a fixed data snapshot.</p><p>For a small reproducible example, use four completed orders with gross amounts of 150, 200, 80, and 70. Their discounts are 15, 20, 8, and 7. Net amounts are therefore 135, 180, 72, and 63. With no filters, the approved Net Sales measure should return 450 USD.</p><p>This arithmetic gives us a reference result. It does not demonstrate that Copilot or another AI system returned it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qJIZoZ7EC9yURAT7rpn3pg.png" /><figcaption>A description-presence check and an answer test support different conclusions. The 450 USD result is an illustrative reference, not an observed AI response. Graphic by theBItoolbox, created with AI assistance.</figcaption></figure><p>Build a small question set around the example:</p><p>1. Ask for total net sales. Check the selected measure and the result of 450.</p><p>2. Ask for sales before discounts. Check that the system uses the gross definition, which totals 500.</p><p>3. Ask for sales by product. Verify the grouping and reconcile the categories to the same total.</p><p>4. Ask for sales last month. Fix the reference date and verify the intended date column and period boundaries.</p><p>5. Ask for a return rate when the example contains no return data. Expect the system to explain that limitation rather than invent a calculation.</p><p>For each test, retain the prompt, expected interpretation, reference query or visual, actual answer, and model version. Inspect generated DAX when the experience exposes it. A matching total alone can hide an incorrect filter or date choice that happens to produce the same result on a small dataset.</p><p>Also test with the intended user permissions. A metadata scan cannot substitute for checking what the deployed identity can access.</p><p>Microsoft notes that Copilot responses can vary even for the same input. Run important questions with realistic variations and preserve the results so later model or configuration changes can be compared. [9]</p><p>You can now make two separate statements: which metadata checks passed, and which answer tests produced the expected behavior. Each has evidence behind it.</p><h3>Make the audit part of model maintenance</h3><p>Once the first review is complete, rerun it when measures, relationships, or business definitions change. Prioritize new findings that affect common questions. For an accepted exception, keep the owner’s rationale with the rule and object so the next reviewer does not have to rediscover it.</p><p>TMDL makes those changes inspectable. The business owner establishes what the model should mean. Runtime tests show whether the AI experience uses that meaning successfully for the questions you actually checked.</p><p>Start with one important question, trace the objects behind it, and preserve the evidence. That produces a more useful improvement plan than a single percentage labeled “AI ready.”</p><h3>References</h3><p>Documentation checked on October 25, 2026. Copilot feature behavior can change.</p><p>1. Microsoft Learn. <a href="https://learn.microsoft.com/en-us/analysis-services/tmdl/tmdl-overview">Tabular Model Definition Language overview</a>.</p><p>2. Microsoft Learn. <a href="https://learn.microsoft.com/en-us/power-bi/create-reports/copilot-evaluate-data">Optimize your semantic model for Copilot in Power BI</a>.</p><p>3. Microsoft Learn. <a href="https://learn.microsoft.com/en-us/power-bi/developer/projects/projects-overview">Power BI Desktop projects</a>.</p><p>4. Microsoft Learn. <a href="https://learn.microsoft.com/en-us/power-bi/developer/projects/projects-dataset">Power BI Desktop project semantic model folder</a>.</p><p>5. Microsoft Learn. <a href="https://learn.microsoft.com/en-us/analysis-services/tmdl/tmdl-how-to">Get started with TMDL</a>.</p><p>6. Microsoft Learn. <a href="https://learn.microsoft.com/en-us/power-bi/guidance/star-schema">Understand star schema and its importance for Power BI</a>.</p><p>7. Microsoft Learn. <a href="https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand">Model relationships in Power BI Desktop</a>.</p><p>8. Microsoft Learn. <a href="https://learn.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering">Bidirectional relationship guidance</a>.</p><p>9. Microsoft Learn. <a href="https://learn.microsoft.com/en-us/power-bi/create-reports/copilot-prepare-data-ai">Prepare your data for AI</a>.</p><p>10. Microsoft Learn. <a href="https://learn.microsoft.com/en-us/power-bi/create-reports/copilot-prepare-data-ai-faq">Frequently asked questions about preparing data for AI</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4a8aec9bf616" width="1" height="1" alt=""><hr><p><a href="https://medium.datadriveninvestor.com/how-to-audit-a-power-bi-semantic-model-using-tmdl-for-ai-4a8aec9bf616">How to Audit a Power BI Semantic Model Using TMDL for AI</a> was originally published in <a href="https://medium.datadriveninvestor.com/">DataDrivenInvestor</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Audit a Power BI Semantic Model Using TMDL]]></title>
            <link>https://levelup.gitconnected.com/how-to-audit-a-power-bi-semantic-model-using-tmdl-e22e24d3956f?source=rss-dd2443488cef------2</link>
            <guid isPermaLink="false">https://medium.com/p/e22e24d3956f</guid>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[data-modeling]]></category>
            <dc:creator><![CDATA[Thebitoolbox]]></dc:creator>
            <pubDate>Wed, 21 Oct 2026 21:19:08 GMT</pubDate>
            <atom:updated>2026-10-21T21:19:08.075Z</atom:updated>
            <content:encoded><![CDATA[<h4><em>Three practical checks for default aggregation, descriptions, and disconnected tables</em></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2pwSbnz856MtnQaElOyvvQ.png" /><figcaption>Hero illustration created with AI for theBItoolbox. Conceptual artwork, not a Power BI screenshot.</figcaption></figure><p><em>Author’s note: I created Semantic AI Auditor. This article was written with AI assistance and uses simplified examples to explain the checks.</em></p><p>While testing the auditor on one of my own Power BI models, I found disconnected tables that needed different decisions. Some supported selectors and were useful. Another belonged to an abandoned report idea and could be removed. A relationship count alone could not tell me which was which.</p><p>That experience shaped the review workflow in this article. Start with a fact you can establish from the model definition, explain why it deserves attention, and then check the business purpose before making a change.</p><p>We will inspect three conditions in TMDL: a numeric identifier configured for summation, missing object descriptions, and a table without an active relationship. You can perform these checks manually. No Copilot setup or paid auditing tool is needed for the walkthrough.</p><h3>Start with the saved model definition</h3><p><a href="https://learn.microsoft.com/en-us/power-bi/developer/projects/projects-overview">Power BI Projects</a> store report and semantic model definitions as text files. Save a copy of your model as a Power BI Project, using <strong>File → Save as → Power BI project (.pbip)</strong> where available. This walkthrough assumes its semantic model is stored as TMDL.</p><p>Inside the .SemanticModel folder, inspect definition/tables for table definitions and definition/relationships.tmdl for relationships. A thin report connected to a remote semantic model may not include the model definition you need; obtain that definition from its owner.</p><p>Save before reviewing. You are inspecting a snapshot on disk. Keep the project under version control or work on a separate copy so you can compare changes.</p><p>The TMDL blocks below are excerpts from table-definition files, not complete replacement files or scripts to paste directly into TMDL view. Preserve the rest of each object’s definition. The examples use tabs for indentation.</p><h3>Check a numeric identifier configured for summation</h3><p>Consider this column in a sales table:</p><pre>table Sales<br> <br> column CustomerID<br> dataType: int64<br> summarizeBy: sum<br> sourceColumn: CustomerID</pre><p>There are three separate pieces of evidence. CustomerID suggests an identifier, int64 establishes a numeric type, and summarizeBy: sum explicitly records a default aggregation.</p><p>The name is a clue that needs confirmation. Once you know the values identify customers, adding identifiers such as 101, 102, and 103 to produce 306 does not answer “How many customers bought something?”</p><p>The <a href="https://learn.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular.column.summarizeby?view=analysisservices-dotnet">SummarizeBy property</a> describes the field’s default aggregation. For this identifier, an appropriate reviewed definition is:</p><pre>table Sales<br> <br> column CustomerID<br> dataType: int64<br> summarizeBy: none<br> sourceColumn: CustomerID</pre><p>In Desktop, you can make the corresponding change through the column’s summarization setting. Microsoft’s <a href="https://learn.microsoft.com/en-us/power-bi/create-reports/service-aggregates">aggregation guidance</a> describes using <strong>Don’t summarize</strong> for numeric IDs.</p><p>For the specific business question “How many distinct customers appear in the current sales selection?”, define a measure deliberately. In this example, assume every sales row has a nonblank customer ID:</p><pre>Customers With Sales =<br> DISTINCTCOUNT ( Sales[CustomerID] )</pre><p>Changing the column default does not rewrite existing measures or guarantee that an existing visual stops summing it. An author can still write SUM ( Sales[CustomerID] ). Check the affected visuals and use the counting measure where it answers the intended question.</p><p>There is also a limit to the audit claim. An omitted property or summarizeBy: default is different from an explicit sum. A file reader should record that distinction instead of inventing an explicit summation setting. My A01 rule requires a known numeric type and an explicit Sum setting before warning about an identifier candidate.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zcPajabMV50Am8XlJ0tZ5A.png" /><figcaption>Figure 1. Change the default after confirming the field’s purpose. The column remains numeric. Technical illustration created for theBItoolbox with AI assistance.</figcaption></figure><h3>Put descriptions on the objects people use</h3><p>A model can contain a useful data-dictionary table with columns named ObjectName and Description, while the actual model objects still have empty Description properties. Those rows are data. They do not populate model metadata automatically.</p><p>Suppose the model contains this measure:</p><pre>table Sales<br> <br> measure &#39;Net Sales&#39; = SUM(Sales[NetAmount])<br> formatString: #,##0.00</pre><p>The expression tells you which column is added. It does not explain what the source system means by NetAmount.</p><p>For this example, assume the model owner confirms that the column contains line amounts after discounts, before tax, with returns stored as negative amounts. A useful description can capture those facts:</p><pre>table Sales<br> <br> /// Sum of line amounts after discounts, before tax.<br> /// Returns are stored as negative amounts.<br> measure &#39;Net Sales&#39; = SUM(Sales[NetAmount])<br> formatString: #,##0.00</pre><p><a href="https://learn.microsoft.com/en-us/analysis-services/tmdl/tmdl-overview">TMDL descriptions</a> use /// immediately above the object declaration. These lines set its Description property; they are not ordinary explanatory comments to ignore during an audit.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*o_qrub8YmUs3awDprbtstg.png" /><figcaption>Figure 2. A description stored in table rows and a model object’s Description are separate things. Technical illustration created for theBItoolbox with AI assistance.</figcaption></figure><p>For a first pass, review visible tables, columns, and measures, including whether the parent table is visible. Look for missing or whitespace-only descriptions. Then look for descriptions that repeat the object’s name after normalizing case and whitespace.</p><p>These checks answer narrow questions. A description reading “Net Sales” adds little to a measure already called Net Sales. A longer description can still be wrong. In particular, do not copy the example’s tax or returns policy into your model without confirming it.</p><p>The improvement comes from making an agreed business definition available at the point of use. A description-coverage percentage only measures how many objects have text.</p><h3>Review disconnected tables in context</h3><p>For the next check, inspect relationship endpoints and their active state. Build a list of eligible visible tables with no active connection. Keep tables with only inactive relationships distinguishable from tables with no relationships at all.</p><p>Neither condition proves that a table is unused. Microsoft documents <a href="https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand#disconnected-tables">disconnected tables</a> as a valid way to accept user input for calculations.</p><p>For example, create a small calculated table for a metric selector:</p><pre>MetricSelector =<br> DATATABLE (<br> &quot;Metric&quot;, STRING,<br> { { &quot;Net Sales&quot; }, { &quot;Orders&quot; } }<br> )</pre><p>Use MetricSelector[Metric] in a slicer with single selection. Define the following measures separately. Assume order IDs are nonblank, and remember that an order can occupy several sales rows:</p><pre>Net Sales = SUM ( Sales[NetAmount] )<br><br>Orders = DISTINCTCOUNT ( Sales[OrderID] )<br><br>Selected Metric =<br> VAR Choice =<br> SELECTEDVALUE ( MetricSelector[Metric], &quot;Net Sales&quot; )<br> RETURN<br> SWITCH (<br> Choice,<br> &quot;Net Sales&quot;, [Net Sales],<br> &quot;Orders&quot;, [Orders],<br> BLANK ()<br> )</pre><p>The slicer filters MetricSelector. The measure reads that choice using <a href="https://learn.microsoft.com/en-us/dax/selectedvalue-function-dax">SELECTEDVALUE</a> and chooses a calculation. There is no relationship from the selector to Sales. In this expression, zero or multiple selected values use the Net Sales fallback.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nkxH7x2GEeY4Gj-MelFzMQ.png" /><figcaption>Figure 3. The dashed arrow is a DAX dependency, not an inactive relationship. Technical illustration created for theBItoolbox with AI assistance.</figcaption></figure><p>Keep this selector disconnected and document its purpose. For production use, also give the selected measure an appropriate format for each branch: a sales amount and an order count have different presentation needs.</p><p>Now consider another disconnected table left over from an abandoned report idea. Its structural finding may look identical. Removing it requires a different investigation: check report usage, dependent calculations, and other consumers of the model. A relationship inventory cannot establish those dependencies by itself.</p><p>In my own review, this distinction mattered more than reducing the warning count. A justified selector can remain a warning under a structural rule while still being the right design.</p><h3>Keep an unknown result visible</h3><p>A useful audit should explain where its evidence stops. I use four outcomes:</p><p>· <strong>WARNING:</strong> the check found a condition to review.</p><p>· <strong>PASS:</strong> this check’s warning condition was not found.</p><p>· <strong>NOT EVALUATED:</strong> the metadata needed for a verdict was unavailable or unsupported.</p><p>· <strong>NOT APPLICABLE:</strong> the object falls outside the check’s scope.</p><p>Unknown does not mean zero. If a check needs a column’s data type and the saved definition does not provide it, a missing value should not become an invented type. Equally, a passing description check says nothing about whether the business definition is accurate.</p><p>After a justified change, save the model, inspect the diff, and repeat the check. Then test representative report behavior. For these examples, that means checking the customer-count measure, confirming the description with its owner, and exercising both selector choices.</p><h3>Automate the inspection as the model grows</h3><p>Manual inspection is a useful way to understand a rule. Repeating it across many tables and measures becomes tedious, which is why I built Semantic AI Auditor.</p><p>It reads supported local TMDL or TMSL definitions and presents findings with evidence, potential impact, and suggested actions. The audit code does not execute the target model’s business queries or change its source files. It also does not establish report usage, certify model quality, or measure Copilot answer accuracy.</p><p>You can use the same review approach with your own scripts. Parse the object structure, keep missing values explicit, and attach the observed properties to each finding. TMDL expressions and descriptions have their own syntax, so a text search is a useful starting point, but it is not a complete parser.</p><p>For your next model handover, pick one numeric key and inspect its aggregation. Ask the model owner to explain one important measure in plain language and put that explanation on the object. Review one disconnected table before deciding what to do with it. Each exercise produces a decision you can explain to the next developer.</p><p><em>If you want the packaged workflow, </em><a href="https://thebitoolbox.gumroad.com/l/semanticaiauditor">Semantic AI Auditor for Power BI</a><em> is my tool. The checks above can also be performed without it.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e22e24d3956f" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/how-to-audit-a-power-bi-semantic-model-using-tmdl-e22e24d3956f">How to Audit a Power BI Semantic Model Using TMDL</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Power BI Model Audit Beyond a Successful Refresh]]></title>
            <link>https://medium.datadriveninvestor.com/power-bi-model-audit-beyond-a-successful-refresh-1e6ffd13eda9?source=rss-dd2443488cef------2</link>
            <guid isPermaLink="false">https://medium.com/p/1e6ffd13eda9</guid>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[microsoft-fabric]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Thebitoolbox]]></dc:creator>
            <pubDate>Mon, 19 Oct 2026 05:13:16 GMT</pubDate>
            <atom:updated>2026-10-19T05:13:16.595Z</atom:updated>
            <content:encoded><![CDATA[<p>Practical metadata checks for your next Power BI model review</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S6hF7V2ucvwzrwbYgciJjQ.png" /></figure><p>A Power BI model can refresh successfully while a new report author still has to guess which field to use. A column name may be familiar, its description may be empty, and its default aggregation may produce a number with no useful business meaning. A metadata review helps identify those decisions before the model is handed over or extended.</p><p>Disclosure: I created Semantic AI Auditor, the commercial tool shown in the screenshots. This article was prepared with AI writing assistance and draws on my model reviews and a synthetic demonstration model. You can perform the checks below manually in Power BI Desktop.</p><p>During testing, I found that the useful part of an audit was the explanation behind each finding. The same structural condition could point to a genuine cleanup task in one table and an intentional design in another. These are three checks I would start with.</p><p><strong>First, review the meaning behind repeated field names.</strong></p><p>My demonstration model contains both DimCategory[CategoryID] and DimSubcategory[CategoryID]. Both descriptions are empty.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hBD2nlaHotqSIVxgrtktQg.png" /><figcaption>Figure 1. Two qualified fields share a name and lack descriptions. Screenshot from the author’s synthetic demo; the highlight identifies the evidence to review.</figcaption></figure><p>The shared name is a normal consequence of this design. One field identifies a category; the other records the category to which a subcategory belongs. The table names and relationship provide useful context. The review question is whether a person opening the model has enough information to understand those roles and choose the intended business attribute.</p><p>I would inspect both columns in Model view, check their relationship, and confirm how report authors are meant to use them. Possible descriptions, once those roles have been verified, are:</p><p>· DimCategory[CategoryID]: “Unique identifier for a product category. Use DimCategory[Category] for category labels in reports.”</p><p>· DimSubcategory[CategoryID]: “Identifier of the parent category for this subcategory. Links to DimCategory[CategoryID].”</p><p>Each description explains a role. Copying “Category ID” into both Description properties would leave most of the original question unanswered.</p><p>I would also review whether report authors need to see both technical keys. Hiding an unnecessary key can make field selection clearer, while the relationship continues to use it. Shared names can remain when they communicate the intended design.</p><p>Microsoft similarly recommends concise descriptions to distinguish fields with similar names in its <a href="https://learn.microsoft.com/en-us/power-bi/create-reports/copilot-semantic-models">guidance on Copilot and semantic models</a>. That makes this review relevant to AI-assisted use too. The screenshot itself demonstrates a metadata condition; it contains no test of a Copilot answer.</p><p><strong>Next, inspect what numeric identifiers are configured to do.</strong></p><p>In the demo, FactSales[CustomerID] is an integer column configured with Sum. The relevant properties in its TMDL definition are:</p><p>column CustomerID<br> dataType: int64<br> isHidden<br> summarizeBy: sum</p><p>This excerpt omits unrelated properties. The column is hidden, which reduces its everyday exposure to report authors, but the serialized Sum setting is still present.</p><p>Adding customer identifiers together has no useful meaning in this model. When reviewing a visible identifier, I would select the column in Desktop and inspect its default summarization. If the field is used as a key or label, Don’t summarize is usually the appropriate starting point. Microsoft’s <a href="https://learn.microsoft.com/en-us/power-bi/create-reports/service-aggregates">aggregation documentation</a> explains how model defaults and visual aggregations interact.</p><p>Changing that default also warrants checking existing visuals and explicit measures. It does not rewrite a measure that already calls SUM on the column.</p><p>If the business needs a customer count, define that calculation explicitly. First establish whether it means customers with transactions, all customers in the customer table, or another population. A distinct count over a sales fact table answers a different question from counting the customer dimension.</p><p>The field’s name is only a clue. Confirm its actual role before changing it. An audit can find identifier candidates configured with Sum; the developer still decides whether the aggregation is inappropriate.</p><p><strong>Then investigate tables without active relationships.</strong></p><p>This check produced a useful lesson when I reviewed one of my own models. A disconnected table supplied a list of columns, with a measure assigned to each choice. Another table supported a slicer idea that had never been completed and was no longer used.</p><p>The first had a purpose. The second was a cleanup candidate. Their relationship diagrams alone could not distinguish them.</p><p>Microsoft documents <a href="https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand#disconnected-tables">disconnected tables as a valid modeling pattern</a>, including tables that accept a selection used by a calculation. Also distinguish a table with no relationships from one whose relationships are all inactive. Calculations can use an inactive relationship deliberately.</p><p>For each candidate, I would check the measures that reference it, its use in slicers or other visuals, and the intended interaction. A table description can record that purpose so the next developer has something concrete to verify.</p><p>If it appears obsolete, I would remove it in a copy of the project and test the affected report flows before changing the maintained version. A metadata finding identifies a place to investigate; establishing that an object is unused requires a separate review of its consumers.</p><p><strong>Turn the findings into a small review list.</strong></p><p>The synthetic Before model used in my demo produces 71 warnings across 53 objects. Some objects trigger more than one check. These counts describe that constructed example; they do not estimate how many issues exist in a typical Power BI model.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3N5AKDSEGopSnRYXZ3wW1Q.png" /><figcaption>Figure 2. The demo overview groups findings by check. Warning and object counts differ because one object can trigger several checks. Screenshot from the author’s demo.</figcaption></figure><p>I built Semantic AI Auditor to collect this evidence in one place. It is a local Power BI template that reads supported saved TMDL or TMSL model definitions. It does not execute the target model’s business queries or change the model. The core audit works without a Copilot subscription.</p><p>For a review session, I would choose one finding, inspect its evidence, and record a decision: make a change, document an intentional exception, or investigate missing information. After a change, save the source model, rerun the check, and test the affected report behavior.</p><p>The separate After copy of the synthetic model produces zero warnings following manual changes. That result means the applicable checks no longer flag those conditions. Business calculations, report behavior, and any AI-generated answers still need their own validation. When the information needed to evaluate a rule is unavailable, the result should remain visibly unevaluated rather than quietly becoming a pass.</p><p>For your next model handover, start with one table that people use frequently. Check whether its descriptions explain the business roles, whether its identifiers have sensible defaults, and whether its relationships match the intended interaction. Record the reason for each decision. That record gives the next developer something more useful than a warning count alone.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1e6ffd13eda9" width="1" height="1" alt=""><hr><p><a href="https://medium.datadriveninvestor.com/power-bi-model-audit-beyond-a-successful-refresh-1e6ffd13eda9">Power BI Model Audit Beyond a Successful Refresh</a> was originally published in <a href="https://medium.datadriveninvestor.com/">DataDrivenInvestor</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Fix DataFormat.Error in Power Query — 6 Common Causes]]></title>
            <link>https://medium.com/@thebitoolbox/how-to-fix-dataformat-error-in-power-query-6-common-causes-fe3fe9d52afe?source=rss-dd2443488cef------2</link>
            <guid isPermaLink="false">https://medium.com/p/fe3fe9d52afe</guid>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[microsoft-power-query]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-cleaning]]></category>
            <dc:creator><![CDATA[Thebitoolbox]]></dc:creator>
            <pubDate>Thu, 17 Sep 2026 13:41:41 GMT</pubDate>
            <atom:updated>2026-09-17T13:41:41.388Z</atom:updated>
            <content:encoded><![CDATA[<h4>The error message is only the symptom. Here’s how to find what is actually breaking your query.</h4><p>DataFormat.Error is one of those Power Query errors that looks simple until you actually try to fix it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*D6H-fkWat7ruFCQ6cucqnw.png" /><figcaption>Generated by ChatGPT</figcaption></figure><p>You might see:</p><pre>DataFormat.Error:<br>We couldn&#39;t convert to Number.</pre><p>or:</p><pre>DataFormat.Error:<br>We couldn&#39;t parse the input provided as a Date value.</pre><p>or even:</p><pre>DataFormat.Error:<br>Invalid cell value &#39;#N/A&#39;.</pre><p>The problem is that <strong>DataFormat.Error is not really one problem</strong>.</p><p>The same error can be caused by bad values, incorrect data types, locale settings, invisible characters, or even an error that already existed in the source.</p><p>So instead of asking:</p><blockquote><em>How do I remove this error?</em></blockquote><p>a better question is:</p><blockquote><em>What assumption is Power Query making that my data violates?</em></blockquote><p>Here are six common places to look.</p><h3>1. Text hiding inside a numeric column</h3><p>Imagine a quantity column like this:</p><pre>12<br>18<br>N/A<br>24<br>31</pre><p>Power Query tries to convert the column to a Whole Number.</p><p>Everything works until it reaches:</p><pre>N/A</pre><p>And you get:</p><pre>We couldn&#39;t convert to Number.</pre><p>The tempting fix is to replace the error with zero.</p><p>But that might be wrong.</p><p>Does N/A mean:</p><ul><li>zero?</li><li>missing?</li><li>not applicable?</li><li>a row that should be removed?</li></ul><p>Those are completely different business meanings.</p><p><strong>Removing the error is not the same as fixing the data.</strong></p><h3>2. The Changed Type step</h3><p>Power Query often adds a Changed Type step automatically.</p><p>That can work perfectly for months.</p><p>Then one day your source changes from:</p><pre>12<br>18<br>25</pre><p>to:</p><pre>12<br>pending<br>25</pre><p>Suddenly the refresh fails.</p><p>Your query did not change.</p><p><strong>Your data did.</strong></p><p>When this happens, move backwards through Applied Steps.</p><p>If everything is fine before Changed Type and errors appear immediately after it, you have found the transformation that introduces the problem.</p><h3>3. Number locale</h3><p>Consider this value:</p><pre>1.397,64</pre><p>In Germany, that means one thousand three hundred ninety-seven and 64 cents.</p><p>In the US, the same number would normally be written:</p><pre>1,397.64</pre><p>If Power Query interprets the source using the wrong locale, conversion can fail.</p><p>Instead of manually replacing commas and dots, use:</p><p><strong>Change Type → Using Locale</strong></p><p>and tell Power Query how the source is actually formatted.</p><p>The important point is that the value itself may be perfectly valid.</p><p>Power Query simply needs to know which convention it is reading.</p><h3>4. Dates are even more dangerous</h3><p>Now consider:</p><pre>02/03/2026</pre><p>In the US:</p><p><strong>February 3, 2026</strong></p><p>In much of Europe:</p><p><strong>2 March, 2026</strong></p><p>Both are valid dates.</p><p>That means Power Query may not throw an error at all.</p><p>And that is worse.</p><p>A value such as:</p><pre>31/01/2026</pre><p>will usually fail under a US interpretation because there is no month 31.</p><p>But 02/03/2026 can quietly become the wrong date.</p><p>So with dates, the problem is not only:</p><p><strong>Wrong locale → DataFormat.Error</strong></p><p>It can also be:</p><p><strong>Wrong locale → wrong data with no error</strong></p><h3>5. Invisible characters</h3><p>Sometimes a value looks completely normal:</p><pre>78211</pre><p>but still refuses to convert to a number.</p><p>Why?</p><p>Because it may actually contain an invisible Unicode character.</p><p>For example, these two values can look identical:</p><pre>78211<br>78211​</pre><p>while one has five characters and the other has six.</p><p>A quick diagnostic is:</p><pre>Text.Length([ProductCode])</pre><p>If a code that should have five digits returns 6, something extra is hiding there.</p><p>For identifiers that are supposed to contain <strong>digits only</strong>, one aggressive option is:</p><pre>Text.Select([ProductCode], {&quot;0&quot;..&quot;9&quot;})</pre><p>But use this carefully.</p><p>Applying the same logic to:</p><pre>-123.45</pre><p>would produce:</p><pre>12345</pre><p>So Text.Select is not a universal DataFormat.Error fix.</p><p>It only makes sense when the business rule says the field should contain digits and nothing else.</p><h3>6. The error may already exist in the source</h3><p>Not every error shown in Power Query was created by Power Query.</p><p>If Excel already contains:</p><pre>#N/A</pre><p>Power Query may simply import that error.</p><p>That distinction matters.</p><p>Otherwise, you can spend time changing a perfectly valid query when the real problem is upstream.</p><p>Before changing transformations, check whether the original value is already broken.</p><p>Whether you replace it with null, keep it, or fix the source depends on what that error actually means.</p><h3>A simple debugging workflow</h3><p>When I see DataFormat.Error, I try not to start randomly changing transformations.</p><p>Instead:</p><p><strong>1. Read the full error message.</strong><br> What conversion failed, and what value caused it?</p><p><strong>2. Find the first step where the error appears.</strong><br> Move backwards through Applied Steps.</p><p><strong>3. Use Keep Errors.</strong><br> Instead of searching through thousands of rows, isolate only the problematic ones.</p><p><strong>4. Check the source.</strong><br> Did Power Query create the bad value, or was it already there?</p><p><strong>5. Fix the assumption, not just the symptom.</strong></p><p>That last step matters most.</p><p>A Power Query error is often useful information. It tells you that your query expected one thing and received something else.</p><p>So the rule I try to follow is:</p><blockquote><strong><em>Don’t fix the error. Fix what caused it.</em></strong></blockquote><blockquote><strong><em>Prefer watching instead?</em></strong><em><br> I cover all six examples step by step in my Power Query DataFormat.Error video.</em></blockquote><iframe src="https://cdn.embedly.com/widgets/media.html?url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dt08Tr-FrTQA&amp;type=text%2Fhtml&amp;schema=youtube&amp;display_name=YouTube&amp;src=https%3A%2F%2Fwww.youtube.com%2Fembed%2Ft08Tr-FrTQA" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/52bb9aa652955875df4719b572225241/href">https://medium.com/media/52bb9aa652955875df4719b572225241/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fe3fe9d52afe" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Calculated Columns vs Measures in Power BI: Choose the Right Calculation Layer]]></title>
            <link>https://levelup.gitconnected.com/calculated-columns-vs-measures-in-power-bi-choose-the-right-calculation-layer-c843b0ed0da6?source=rss-dd2443488cef------2</link>
            <guid isPermaLink="false">https://medium.com/p/c843b0ed0da6</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Thebitoolbox]]></dc:creator>
            <pubDate>Mon, 31 Aug 2026 15:11:25 GMT</pubDate>
            <atom:updated>2026-08-31T15:11:25.817Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b4TttNxaD0Lu_LzC7RXY2A.png" /><figcaption>Generated with ChatGPT</figcaption></figure><p>Calculated columns are not bad.</p><p>Measures are not always better.</p><p>Power Query is not automatically the right answer either.</p><p>The real mistake is simpler:</p><blockquote><strong><em>Creating a calculated column before asking whether the calculation should be a column at all.</em></strong></blockquote><p>That question matters because Power BI gives you several places to implement logic:</p><ul><li>the source system or warehouse,</li><li>Power Query,</li><li>a DAX calculated column,</li><li>a DAX measure.</li></ul><p>They are not interchangeable.</p><p>They run at different stages, behave differently in reports, and have different implications for model size, refresh, and maintainability.</p><p>Microsoft’s current Power BI guidance makes the distinction clear: DAX calculated columns are evaluated row by row, calculated during refresh, and their results are stored in the model. Measures, in contrast, are calculated as needed and respond to the current report selections and filter context.</p><p>So instead of memorizing:</p><blockquote><em>Calculated column = bad<br>Measure = good</em></blockquote><p>use a better rule:</p><blockquote><strong><em>Choose the right calculation in the right layer.</em></strong></blockquote><h3>One innocent calculated column</h3><p>Imagine a simple FactSales table:</p><pre>SalesID<br>OrderDate<br>ProductID<br>CustomerID<br>SalesAmount<br>CostAmount</pre><p>You want profit, so you create:</p><pre>Line Profit =<br>FactSales[SalesAmount]<br>    - FactSales[CostAmount]</pre><p>It is one line of DAX.</p><p>It looks harmless.</p><p>And on a small table, it probably is.</p><p>Suppose the demo table has only 60 rows.</p><p>Power BI calculates:</p><pre>Row 1 → Line Profit<br>Row 2 → Line Profit<br>Row 3 → Line Profit<br>...<br>Row 60 → Line Profit</pre><p>You now have 60 calculated results.</p><p>Scale exactly the same idea to a 60-million-row fact table:</p><pre>60,000,000 rows<br>        ↓<br>60,000,000 calculated values</pre><p>The formula did not become more complicated.</p><p>The <strong>table did</strong>.</p><p>Microsoft documents that calculated columns extend a model table by evaluating an expression row by row. Their results are calculated during refresh and stored in the model file, which means they consume model space and add work to refresh processing.</p><p>That does not make the column wrong.</p><p>It means the column needs to justify its existence.</p><h3>What actually happens when you create a calculated column?</h3><p>Power BI calculated columns use DAX to create new values based on data already loaded into the model. The formula is applied across the rows of the table, creating a result for each row.</p><p>For example:</p><pre>SalesAmount    CostAmount    Line Profit<br>-----------------------------------------<br>600            408           192<br>299            203            96<br>450            306           144</pre><p>Conceptually:</p><pre>600 - 408 → 192<br>299 - 203 → 96<br>450 - 306 → 144</pre><p>The result becomes another column in the semantic model.</p><p>Microsoft’s Import-model guidance says the VertiPaq storage engine stores DAX calculated columns similarly to columns coming from Power Query, though calculated-column storage structures are built later in processing and typically compress less efficiently than Power Query-sourced columns. That can also extend refresh times.</p><p>That gives us the first important distinction:</p><pre>CALCULATED COLUMN<br>→ row-level evaluation<br>→ refresh-time work<br>→ stored result</pre><p>A measure works differently.</p><h3>The same result can sometimes be a measure</h3><p>Let’s continue with Line Profit.</p><p>Suppose the report needs only total profit by Category.</p><p>You create the calculated column:</p><pre>Line Profit =<br>FactSales[SalesAmount]<br>    - FactSales[CostAmount]</pre><p>Then the visual sums it:</p><pre>Category        Sum of Line Profit<br>----------------------------------<br>Electronics          2,450<br>Home                 1,820<br>Sports               1,310</pre><p>Nothing is wrong with the result.</p><p>It works.</p><p>But now ask:</p><blockquote><strong><em>Do I actually need the individual Line Profit value for every sales row?</em></strong></blockquote><p>If the reporting requirement is simply total profit, we can instead create:</p><pre>Sales Amount :=<br>SUM ( FactSales[SalesAmount] )</pre><pre>Total Cost :=<br>SUM ( FactSales[CostAmount] )</pre><p>and:</p><pre>Profit :=<br>[Sales Amount] - [Total Cost]</pre><p>Add [Profit] next to the existing summed column:</p><pre>Category        Sum of Line Profit        Profit<br>------------------------------------------------<br>Electronics           2,450               2,450<br>Home                  1,820               1,820<br>Sports                1,310               1,310</pre><p>Same business result.</p><p>Different modeling decision.</p><p>Measures are calculated as needed and respond to report selections rather than storing one result for every source row.</p><p>So:</p><pre>CALCULATED COLUMN<br>stored row-level values</pre><p>versus:</p><pre>MEASURE<br>dynamic result in filter context</pre><p>If Line Profit is never needed as an actual field, the calculated column may be unnecessary.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/431/1*QSeUmK0CT115Fmkf81r6Aw.png" /><figcaption>Same result. Different modeling choice.</figcaption></figure><h3>Don’t guess the cost</h3><p>It is tempting to say:</p><blockquote><em>“Calculated columns are expensive.”</em></blockquote><p>That is too broad.</p><p>The cost depends on the actual column and the table it belongs to.</p><p>Factors include:</p><ul><li>number of rows,</li><li>data type,</li><li>cardinality,</li><li>value distribution,</li><li>compression efficiency.</li></ul><p>VertiPaq stores each column using its own internal structures, and Microsoft notes that high-cardinality or text columns can have very different storage characteristics from low-cardinality numeric columns.</p><p>So instead of assuming:</p><pre>CALCULATED COLUMN = HUGE</pre><p>use:</p><pre>CALCULATED COLUMN<br>        ↓<br>MEASURE THE ACTUAL COST</pre><p>Tools such as DAX Studio and VertiPaq Analyzer can help show how much space tables and columns consume.</p><p>In a tiny demo model, the difference may be negligible.</p><p>That is fine.</p><p>Do not fake impressive numbers.</p><p>The lesson is that the same design decision becomes more important as the table grows.</p><h3>A different mistake: duplicating dimension attributes into the fact table</h3><p>Consider this model:</p><pre>DimCustomer<br>-------------<br>CustomerID<br>CustomerName<br>City<br>Country</pre><p>with:</p><pre>DimCustomer<br>     ↓<br>FactSales</pre><p>Country already exists in the Customer dimension.</p><p>Then someone creates:</p><pre>Customer Country =<br>RELATED ( DimCustomer[Country] )</pre><p>inside FactSales.</p><p>Now:</p><pre>USA<br>USA<br>USA<br>Canada<br>USA<br>UK<br>Canada<br>USA<br>...</pre><p>is repeated across every relevant sales row.</p><p>Why?</p><p>If the only requirement is a Country slicer, the model already has:</p><pre>DimCustomer[Country]</pre><p>Use it.</p><p>Calculated columns can reference related tables and model relationships, which is one of their legitimate capabilities.</p><p>But the fact that DAX <em>can</em> copy an attribute into a fact table does not mean that doing so improves the model.</p><p>A useful rule is:</p><blockquote><strong><em>If the attribute already belongs to a dimension, ask why the fact table needs another copy.</em></strong></blockquote><h3>Sometimes you genuinely need a column</h3><p>Now consider a different requirement.</p><p>You want users to filter transactions by order size:</p><pre>Small<br>Large</pre><p>You create:</p><pre>Order Size Band =<br>IF (<br>    FactSales[SalesAmount] &gt;= 500,<br>    &quot;Large&quot;,<br>    &quot;Small&quot;<br>)</pre><p>Then you place Order Size Band in a slicer.</p><p>This is different from total profit.</p><p>You do not just need a result.</p><p>You need a <strong>field</strong>.</p><p>Microsoft specifically documents that calculated columns can be used in slicers, filters, rows, and columns in visuals, while measures are primarily used as values and visual-level filters.</p><p>Calculated columns are also commonly used where a new categorical field is needed for rows, axes, legends, or grouping.</p><p>So in this case:</p><pre>DO WE NEED A COLUMN?</pre><p>Yes.</p><p>But that leads to a second question:</p><blockquote><strong><em>Where should the column be created?</em></strong></blockquote><h3>Needing a column does not automatically mean needing DAX</h3><p>Our Order Size Band is simple, static row-level logic.</p><p>The result is determined entirely from:</p><pre>SalesAmount</pre><p>and does not need to change when a user clicks a slicer.</p><p>So we have several possible layers:</p><pre>SOURCE / WAREHOUSE<br>        ↓<br>POWER QUERY<br>        ↓<br>DAX CALCULATED COLUMN</pre><p>Which one is always correct?</p><p>None of them.</p><p>It depends.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Qusk2g_vlas_W7bT3QkNEw.png" /></figure><h3>Source or warehouse</h3><p>If you control the data platform, stable transformations can often be implemented upstream.</p><p>That could mean:</p><ul><li>SQL,</li><li>warehouse transformation,</li><li>lakehouse layer,</li><li>ETL/ELT pipeline,</li><li>governed source view.</li></ul><p>For example:</p><pre>CASE<br>    WHEN SalesAmount &gt;= 500 THEN &#39;Large&#39;<br>    ELSE &#39;Small&#39;<br>END</pre><p>This can make the rule reusable beyond a single Power BI model and keep semantic-model logic focused on analytical behavior.</p><p>But source-level transformation is not always practical.</p><p>You may not own the database.</p><p>The data team may have a long change process.</p><p>The requirement may be specific to one model.</p><p>That is where Power Query becomes useful.</p><h3>Power Query</h3><p>The same logic can be created as a custom column:</p><pre>Table.AddColumn(<br>    Source,<br>    &quot;Order Size Band&quot;,<br>    each<br>        if [SalesAmount] &gt;= 500<br>        then &quot;Large&quot;<br>        else &quot;Small&quot;,<br>    type text<br>)</pre><p>That column will still be stored in the model.</p><p>This is important.</p><p>Moving the calculation from DAX to Power Query does <strong>not</strong> mean:</p><blockquote><em>“The column now costs zero memory.”</em></blockquote><p>It is still a model column.</p><p>The difference is <strong>where the transformation happens</strong>.</p><p>Microsoft’s Import-model optimization guidance explicitly recommends preferring Power Query custom columns when possible because DAX calculated columns are generally less efficient to store and can lengthen refresh processing.</p><p>Power Query can also sometimes fold transformations back to a capable source system. Query folding translates supported M steps into operations the source can execute, allowing some work to be pushed upstream instead of performed by the Power Query engine.</p><p>But query folding depends on:</p><ul><li>the connector,</li><li>the data source,</li><li>the transformations used.</li></ul><p>It can be full, partial, or unavailable.</p><p>So the correct rule is not:</p><blockquote><em>Always use Power Query.</em></blockquote><p>It is:</p><blockquote><strong><em>Push stable logic upstream when it makes architectural and operational sense.</em></strong></blockquote><h3>When does a DAX calculated column genuinely make sense?</h3><p>There are valid scenarios.</p><p>Calculated columns are not something you should be afraid to use.</p><h3>1. You need a categorical field</h3><p>For example:</p><pre>Small<br>Medium<br>Large</pre><p>and that field must be used in:</p><ul><li>a slicer,</li><li>an axis,</li><li>a grouping,</li><li>rows or columns of a visual.</li></ul><p>Calculated columns support these uses directly.</p><h3>2. You need a sort column</h3><p>Suppose you have:</p><pre>January<br>February<br>March</pre><p>but need a numeric field to support the intended sort order.</p><p>A genuine column can be required.</p><h3>3. You need a real row-level attribute</h3><p>Maybe the model genuinely needs a persistent classification:</p><pre>High Risk<br>Medium Risk<br>Low Risk</pre><p>or:</p><pre>Legacy Customer<br>New Customer</pre><p>If the field is part of how users navigate and filter the model, a column may be appropriate.</p><h3>4. The logic needs model relationships or DAX-specific functionality</h3><p>Calculated columns can reference other tables and relationships in the semantic model.</p><p>Microsoft’s Import-model guidance explicitly acknowledges that calculated columns can be the better choice when a calculation needs model functionality or DAX capabilities that are not practical upstream.</p><p>That is why:</p><blockquote><strong><em>COLUMN BY DESIGN — NOT BY HABIT</em></strong></blockquote><p>is a much better principle than:</p><blockquote><em>Never create calculated columns.</em></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tUkTrakLzn2MN_EjHoXwiQ.png" /></figure><h3>Refresh time vs query time</h3><p>This is one of the most important differences between calculated columns and measures.</p><h3>Calculated column</h3><p>Conceptually:</p><pre>REFRESH<br>   ↓<br>evaluate row 1<br>evaluate row 2<br>evaluate row 3<br>...<br>   ↓<br>store results</pre><p>A DAX calculated column is static from the report user’s perspective. It is calculated as part of data refresh and stored in the model.</p><p>So:</p><pre>CALCULATED COLUMN</pre><pre>REFRESH TIME<br>ROW LEVEL<br>STORED</pre><h3>Measure</h3><p>A measure waits until a report asks for a result.</p><p>For example:</p><pre>Profit :=<br>[Sales Amount] - [Total Cost]</pre><p>A user selects:</p><pre>Category = Electronics</pre><p>The measure evaluates in that context.</p><p>Then the user selects:</p><pre>Category = Home</pre><p>It evaluates again.</p><p>Microsoft describes measures as calculations performed as needed that respond to report selections; their results are not precalculated and stored in the same way as calculated-column values.</p><p>So:</p><pre>MEASURE</pre><pre>QUERY TIME<br>FILTER CONTEXT<br>DYNAMIC</pre><p>Neither behavior is universally superior.</p><p>They solve different problems.</p><h3>Four questions before creating a calculated column</h3><p>This is the framework I would use during model review.</p><h3>1. Do I actually need a column?</h3><p>Will the field be used for:</p><ul><li>slicing,</li><li>grouping,</li><li>axes,</li><li>sorting,</li><li>row-level categorization?</li></ul><p>If not, pause.</p><h3>2. Could it be a measure?</h3><p>If the real requirement is:</p><blockquote><em>“Calculate a result for the current report context,”</em></blockquote><p>a measure might be a better fit.</p><p>Examples:</p><pre>Profit<br>Margin %<br>Average Price<br>Return Rate<br>Target Achievement</pre><p>These are typically analytical results rather than attributes.</p><h3>3. Could the logic happen upstream?</h3><p>If the logic is static and deterministic at refresh time, ask whether it belongs in:</p><pre>SOURCE</pre><p>or:</p><pre>POWER QUERY</pre><p>before defaulting to a DAX column.</p><p>That does not mean “always push everything upstream.”</p><p>It means consider the layer intentionally.</p><h3>4. What will it cost?</h3><p>If you do need the column:</p><ul><li>Which table will contain it?</li><li>How many rows does that table have?</li><li>What is the cardinality?</li><li>What data type will it use?</li><li>What does VertiPaq show for the actual size?</li><li>Does it materially affect refresh?</li></ul><p>Do not optimize hypothetical problems.</p><p>Measure the real model.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UW9nzg5mSifv5UuKmid-Wg.png" /></figure><h3>Common misconceptions</h3><h3>“Calculated columns are bad for performance.”</h3><p>Too broad.</p><p>Some calculated columns are tiny and completely reasonable.</p><p>The mistake is treating their cost as irrelevant.</p><h3>“Measures should replace every calculated column.”</h3><p>No.</p><p>A measure cannot simply replace every field required for slicing, grouping, axes, or other categorical model behavior. Power BI explicitly allows calculated columns in those field roles.</p><h3>“Power Query columns don’t consume model memory.”</h3><p>False.</p><p>Power Query custom columns are still loaded as table columns in Import models and therefore become part of the model structure. Microsoft documents that custom columns are also computed during refresh and stored in the model.</p><p>The advantage is not magical zero storage.</p><p>It is a different processing layer, with potential benefits including better compression and, in supported scenarios, query folding.</p><h3>“If the DAX works, the modeling choice is fine.”</h3><p>Not necessarily.</p><p>This works:</p><pre>Customer Country =<br>RELATED ( DimCustomer[Country] )</pre><p>But if Country already belongs to the Customer dimension and all you need is a slicer, the extra fact-table column may add no useful capability.</p><p>Correct syntax does not guarantee good architecture.</p><h3>“Move everything to the source.”</h3><p>Also too simplistic.</p><p>Sometimes you do not control the source.</p><p>Sometimes Power Query is the practical layer.</p><p>Sometimes the logic genuinely depends on semantic-model relationships or DAX functionality.</p><p>Architecture is about choosing deliberately, not following slogans.</p><h3>Enterprise calculated-column review checklist</h3><p>When reviewing a larger semantic model, I would classify each calculated column using four categories.</p><h3>Purpose</h3><ul><li>Is this used in a slicer?</li><li>Axis?</li><li>Sort?</li><li>Grouping?</li><li>Relationship?</li><li>Security logic?</li><li>Other model behavior?</li></ul><h3>Replaceability</h3><ul><li>Could the reporting requirement be satisfied by a measure?</li><li>Does the report ever need the individual row-level result?</li></ul><h3>Placement</h3><ul><li>Could this attribute be supplied by the source?</li><li>Could it be created in Power Query?</li><li>Does it require DAX/model context?</li></ul><h3>Cost</h3><ul><li>Which table contains it?</li><li>How many rows?</li><li>What cardinality?</li><li>What data type?</li><li>What is its actual VertiPaq size?</li><li>Does refresh materially change after adding/removing it?</li></ul><p>A calculated column should survive review because it has a reason to exist.</p><p>Not because:</p><blockquote><em>“That’s how we’ve always done it.”</em></blockquote><h3>Auditing larger Power BI models</h3><p>Finding one calculated column is easy.</p><p>Reviewing dozens across a semantic model is different.</p><p>Imagine a model containing:</p><pre>Calculated Column<br>Calculated Column<br>Calculated Column<br>Calculated Column<br>Calculated Column<br>...</pre><p>The correct response is not:</p><blockquote><em>Delete all of them.</em></blockquote><p>It is:</p><pre>FIND<br> ↓<br>CLASSIFY<br> ↓<br>REVIEW</pre><p>A useful audit should tell you:</p><ul><li>which calculated columns exist,</li><li>which tables they belong to,</li><li>what expressions they contain,</li><li>what other objects they depend on,</li><li>whether they appear to serve a model purpose.</li></ul><p>Then a developer makes the decision.</p><h3>Where ModelLens fits</h3><p>This is the type of review where model metadata becomes useful.</p><p>In a small PBIX, you can click through tables manually.</p><p>In a large semantic model, understanding every calculated column, expression, relationship, and dependency becomes tedious.</p><p>ModelLens can help surface model metadata such as calculated columns, expressions, tables, dependencies, and referenced objects so you can build a review list.</p><p>The point is not:</p><blockquote><strong><em>Calculated Column = Error</em></strong></blockquote><p>It is:</p><blockquote><strong><em>Calculated Column = Candidate for Review</em></strong></blockquote><p>You still decide whether it belongs.</p><p><strong>ModelLens:</strong><br><a href="https://thebitoolbox.gumroad.com/l/modellens">https://thebitoolbox.gumroad.com/l/modellens</a></p><h3>Choose the right layer</h3><p>Instead of memorizing one rigid Power BI rule, use this mental model:</p><pre>DYNAMIC BUSINESS RESULT<br>        ↓<br>     MEASURE</pre><pre>STATIC ROW ATTRIBUTE<br>        ↓<br>SOURCE / POWER QUERY<br>when practical</pre><pre>GENUINE MODEL-LEVEL ROW LOGIC<br>        ↓<br>CALCULATED COLUMN</pre><p>There will be exceptions.</p><p>That is fine.</p><p>The goal is not to eliminate DAX calculated columns.</p><p>The goal is to stop creating them automatically.</p><h3>Final rule</h3><p>Do not ask:</p><blockquote><em>“How do I create this calculated column?”</em></blockquote><p>Start one step earlier:</p><blockquote><strong><em>Do I need a column at all?</em></strong></blockquote><p>Then ask:</p><blockquote><em>Could this be a measure?</em></blockquote><p>Then:</p><blockquote><em>Could this logic happen upstream?</em></blockquote><p>And finally:</p><blockquote><em>If I really need this column, what will it cost?</em></blockquote><p>Calculated columns are useful.</p><p>Measures are useful.</p><p>Power Query is useful.</p><p>The source layer is useful.</p><p>The mistake is not the feature.</p><blockquote><strong><em>It is putting the wrong calculation in the wrong layer.</em></strong></blockquote><h3>Watch the full example</h3><p>In the video version of this article, I walk through:</p><ul><li>a row-by-row Line Profit calculated column,</li><li>60 rows versus production-scale fact tables,</li><li>the same result calculated with a measure,</li><li>actual column-cost inspection,</li><li>an unnecessary RELATED() column,</li><li>a valid slicer field,</li><li>Power Query versus DAX,</li><li>refresh time versus query time,</li><li>and a four-question decision framework.</li></ul><iframe src="https://cdn.embedly.com/widgets/media.html?url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DEzTYx3JRmI8&amp;type=text%2Fhtml&amp;schema=youtube&amp;display_name=YouTube&amp;src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FEzTYx3JRmI8" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/e2aef4c713aedaf56f5a8fa723d78ae4/href">https://medium.com/media/e2aef4c713aedaf56f5a8fa723d78ae4/href</a></iframe><h3>Quick checklist</h3><p>Before creating your next calculated column, ask:</p><ol><li><strong>Do I need a column?</strong></li><li><strong>Could it be a measure?</strong></li><li><strong>Could it happen upstream or in Power Query?</strong></li><li><strong>What will it cost in this table?</strong></li></ol><p>If you can answer those four questions, you are no longer choosing a feature out of habit.</p><p>You are designing the model.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c843b0ed0da6" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/calculated-columns-vs-measures-in-power-bi-choose-the-right-calculation-layer-c843b0ed0da6">Calculated Columns vs Measures in Power BI: Choose the Right Calculation Layer</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[DIVIDE vs “/” in DAX: How to Handle Zero and BLANK Denominators Safely]]></title>
            <link>https://levelup.gitconnected.com/divide-vs-in-dax-how-to-handle-zero-and-blank-denominators-safely-15031c6b3f40?source=rss-dd2443488cef------2</link>
            <guid isPermaLink="false">https://medium.com/p/15031c6b3f40</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Thebitoolbox]]></dc:creator>
            <pubDate>Mon, 17 Aug 2026 15:18:59 GMT</pubDate>
            <atom:updated>2026-08-17T15:18:59.840Z</atom:updated>
            <content:encoded><![CDATA[<h4>One character can work perfectly for months — until the filter context changes</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QoRywUx9sBDf48lO5c5_ig.png" /><figcaption>Generated with ChatGPT</figcaption></figure><p>A DAX measure can look completely correct.</p><p>It can work in development.</p><p>It can work for almost every customer, product, region, and month.</p><p>Then one day, someone changes a slicer.</p><p>And suddenly the report returns:</p><blockquote>Infinity</blockquote><p>The formula might be as simple as:</p><pre>Target Variance % :=<br>[Target Variance] / [Sales Target]</pre><p>Nothing about the formula changed.</p><p>The <strong>filter context</strong> did.</p><p>That distinction matters because the / operator is not inherently wrong. The problem appears when the denominator is dynamic and can become zero or BLANK() under a particular filter context.</p><p>Microsoft’s guidance is straightforward: use DIVIDE() when the denominator is an expression that can return zero or blank. When the denominator is a known non-zero constant, the / operator is appropriate and avoids unnecessary checking.</p><p>So the real rule is not:</p><blockquote><em>Never use </em><em>/ in DAX.</em></blockquote><p>It is:</p><blockquote><strong><em>Understand your denominator.</em></strong></blockquote><h3>A simple case study</h3><p>Consider a performance model with these regions:</p><pre>North<br>South<br>East<br>West<br>Central</pre><p>The dimension table contains all five regions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/79/1*u95tlDklDnP0J9cGIvP2Ow.png" /></figure><p>The fact table contains data for only four of them.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/271/1*vxZhv2QdmHstoKgJNyenow.png" /></figure><p>There is deliberately <strong>no fact row for Central</strong>.</p><p>The relationship is:</p><pre>DimRegion[Region]<br>        1<br>        │<br>        *<br>FactPerformance[Region]</pre><p>We create these base measures:</p><pre>Actual Sales :=<br>SUM ( FactPerformance[Actual Sales] )</pre><pre>Sales Target :=<br>SUM ( FactPerformance[Sales Target] )</pre><pre>Target Variance :=<br>[Actual Sales] - [Sales Target]</pre><p>And our first percentage measure:</p><pre>Target Variance % Unsafe :=<br>[Target Variance] / [Sales Target]</pre><p>For most regions, everything looks fine.</p><p>North:</p><pre>Actual Sales   = 120,000<br>Sales Target   = 100,000<br>Variance       = 20,000<br>Variance %     = 20%</pre><p>South:</p><pre>Actual Sales   = 95,000<br>Sales Target   = 100,000<br>Variance       = -5,000<br>Variance %     = -5%</pre><p>West:</p><pre>Actual Sales   = 110,000<br>Sales Target   = 105,000<br>Variance %     = 4.76%</pre><p>Then we reach East.</p><pre>Actual Sales = 65,000<br>Sales Target = 0</pre><p>The denominator is zero.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XWfqz4uHkYJ6c2wGJWpi8A.png" /><figcaption>The calculation works until the denominator reaches zero.</figcaption></figure><p>That is where the simple slash operator becomes problematic.</p><h3>The slash operator is not the mistake</h3><p>Before going further, this needs to be clear.</p><p>This is valid DAX:</p><pre>Sales in Thousands :=<br>[Actual Sales] / 1000</pre><p>Why?</p><p>Because the denominator is:</p><pre>1000</pre><p>It is:</p><ul><li>constant,</li><li>known,</li><li>non-zero,</li><li>independent of filter context.</li></ul><p>Microsoft specifically recommends / when the denominator is a constant because the division is guaranteed to succeed and does not require the additional denominator checks performed by DIVIDE().</p><p>So this:</p><pre>[Sales] / 1000</pre><p>is completely reasonable.</p><p>This:</p><pre>[Profit] / [Sales]</pre><p>deserves more thought.</p><p>Because [Sales] is dynamic.</p><p>Its value depends on context.</p><p>And context changes.</p><h3>The formula stays the same. The denominator does not.</h3><p>This is one of the easiest DAX mistakes to miss.</p><p>Suppose the report initially shows all regions.</p><p>Your denominator might be:</p><pre>305,000</pre><p>The measure works.</p><p>Then the user selects North.</p><p>Now the denominator becomes:</p><pre>100,000</pre><p>Still fine.</p><p>The user selects East.</p><p>Now:</p><pre>Sales Target = 0</pre><p>Problem.</p><p>Then they select Central.</p><p>There are no matching fact rows.</p><p>Now:</p><pre>Sales Target = BLANK()</pre><p>The DAX expression itself never changed.</p><p>Only the filter context changed.</p><p>That is why dynamic denominators deserve defensive handling.</p><pre>SAME FORMULA</pre><pre>North   → denominator = 100,000<br>East    → denominator = 0<br>Central → denominator = BLANK</pre><p>Microsoft recommends DIVIDE() precisely for expressions whose denominator may return zero or BLANK.</p><h3>Enter DIVIDE()</h3><p>Instead of:</p><pre>Target Variance % :=<br>[Target Variance] / [Sales Target]</pre><p>use:</p><pre>Target Variance % :=<br>DIVIDE (<br>    [Target Variance],<br>    [Sales Target]<br>)</pre><p>DIVIDE() accepts:</p><pre>DIVIDE (<br>    numerator,<br>    denominator,<br>    optional alternate result<br>)</pre><p>When no alternate result is provided, the default result for division by zero is BLANK().</p><p>In our example:</p><pre>East<br>Sales Target = 0</pre><p>the safe version returns:</p><pre>BLANK</pre><p>instead of an unusable division result.</p><p>That might initially seem like a cosmetic difference.</p><p>It is not.</p><h3>BLANK and zero mean different things</h3><p>This deserves more attention than it usually gets.</p><p>Compare:</p><pre>Result = BLANK</pre><p>with:</p><pre>Result = 0%</pre><p>They communicate different business meanings.</p><h3>BLANK</h3><p>Usually means:</p><blockquote><em>A meaningful percentage cannot be calculated.</em></blockquote><p>Perhaps:</p><ul><li>there is no denominator,</li><li>there is no data,</li><li>the calculation is not applicable,</li><li>or the required context does not exist.</li></ul><h3>Zero</h3><p>Means:</p><blockquote><em>The result of the calculation is exactly zero.</em></blockquote><p>Those statements are not interchangeable.</p><p>For East:</p><pre>Actual Sales = 65,000<br>Target       = 0</pre><p>A variance percentage is mathematically undefined because there is no meaningful non-zero target to compare against.</p><p>Returning:</p><pre>0%</pre><p>could imply:</p><blockquote><em>Actual Sales exactly matched Target.</em></blockquote><p>That would be false.</p><p>Microsoft recommends returning BLANK when a measure cannot return a meaningful value.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bcCstuCXalyU3WyDItJAAw.png" /></figure><h3>The third argument of DIVIDE</h3><p>You can provide an alternate result:</p><pre>Target Variance % :=<br>DIVIDE (<br>    [Target Variance],<br>    [Sales Target],<br>    0<br>)</pre><p>That tells DIVIDE() to return zero when division by zero would otherwise fail.</p><p>For East:</p><pre>Numerator   = 65,000<br>Denominator = 0</pre><p>the result becomes:</p><pre>0%</pre><p>This might be exactly what the business wants.</p><p>Or it might be completely wrong.</p><p>The function cannot decide the business meaning for you.</p><p>Also, the alternate result passed directly to DIVIDE() must be a constant.</p><p>So this is valid:</p><pre>DIVIDE (<br>    [Profit],<br>    [Sales],<br>    0<br>)</pre><p>but the fallback is not intended as an arbitrary dynamic measure expression.</p><p>The bigger lesson is:</p><blockquote><strong><em>Do not use the third argument simply because empty cells make you uncomfortable.</em></strong></blockquote><p>Use it only when the fallback is semantically correct.</p><h3>East and Central are not the same problem</h3><p>This is where the example becomes more interesting.</p><p>Consider East:</p><pre>FACT ROW EXISTS<br><br>Actual Sales = 65,000<br>Sales Target = 0</pre><p>Now compare Central:</p><pre>NO FACT ROW EXISTS<br><br>Actual Sales = BLANK<br>Sales Target = BLANK</pre><p>These situations can look similar in a report because both can produce an empty result.</p><p>But they represent different data states.</p><p>East says:</p><blockquote><em>We have data, and the target is explicitly zero.</em></blockquote><p>Central says:</p><blockquote><em>There is no matching fact data.</em></blockquote><p>That distinction matters.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Mmb1c1-NHK_J9jvghPI8Rg.png" /></figure><h3>What do the different approaches return?</h3><p>That fourth version is important.</p><pre>Target Variance % Forced Zero :=<br>COALESCE (<br>    DIVIDE (<br>        [Target Variance],<br>        [Sales Target]<br>    ),<br>    0<br>)</pre><p>COALESCE() operates on the <strong>final result</strong>.</p><p>If the DIVIDE() expression returns blank, COALESCE() replaces it with zero.</p><p>So Central becomes:</p><pre>0%</pre><p>But now we have to ask:</p><blockquote><em>Is Central really zero percent?</em></blockquote><p>No.</p><p>There is no result.</p><p>There is no fact row.</p><p>We turned:</p><pre>NO DATA</pre><p>into:</p><pre>0%</pre><p>That might make the visual look fuller.</p><p>It does not make the result more accurate.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uH3bTLn6BXYsnh6nO61QFg.png" /></figure><h3>Why BLANK can also be better for performance</h3><p>There is another reason to think carefully before converting every blank into a value.</p><p>Power BI report visuals normally eliminate groupings where the measure returns BLANK.</p><p>That behavior helps keep result sets sparse.</p><p>Microsoft explicitly recommends returning BLANK when a meaningful value cannot be produced because it allows report visuals to avoid rendering unnecessary groupings.</p><p>Imagine a Product dimension containing:</p><pre>50,000 products</pre><p>Only:</p><pre>2,000</pre><p>have sales in the selected month.</p><p>If your measure returns BLANK for the other 48,000 products, Power BI can normally avoid displaying those empty groups.</p><p>If you explicitly convert every blank to zero:</p><pre>Sales Forced :=<br>COALESCE (<br>    [Sales],<br>    0<br>)</pre><p>you may turn an otherwise sparse result into a dense one.</p><p>Instead of:</p><pre>Product A   120<br>Product D    85<br>Product X   240</pre><p>the result can conceptually become:</p><pre>Product A     120<br>Product B       0<br>Product C       0<br>Product D      85<br>Product E       0<br>...</pre><p>The business may genuinely require that.</p><p>But it should be intentional.</p><p>Microsoft warns that converting blanks to values can lead to inefficient and poor report designs because more groups may need to be returned and rendered.</p><h3>What if users genuinely need to see empty categories?</h3><p>Sometimes that is the requirement.</p><p>For example:</p><blockquote><em>Show all regions even if they have no sales.</em></blockquote><p>You do not necessarily need to change the business meaning of the measure to achieve that.</p><p>Power BI includes the <strong>Show items with no data</strong> option for visual groupings. It can display categories that do not contain measure data.</p><p>For example:</p><pre>North       20%<br>South       -5%<br>East        BLANK<br>West        4.76%<br>Central     BLANK</pre><p>Central can remain visible without pretending its result is zero.</p><p>That separation is useful:</p><pre>VISUAL REQUIREMENT<br>Show the category</pre><pre>MEASURE REQUIREMENT<br>Preserve the real calculation result</pre><p>Do not automatically solve the first problem by corrupting the semantics of the second.</p><h3>Manual IF vs DIVIDE()</h3><p>You could write:</p><pre>Target Variance % Manual :=<br>IF (<br>    OR (<br>        ISBLANK ( [Sales Target] ),<br>        [Sales Target] = 0<br>    ),<br>    BLANK (),<br>    [Target Variance] / [Sales Target]<br>)</pre><p>This works.</p><p>It explicitly:</p><ol><li>checks for blank,</li><li>checks for zero,</li><li>returns blank if either test succeeds,</li><li>otherwise performs division.</li></ol><p>But compare that with:</p><pre>Target Variance % :=<br>DIVIDE (<br>    [Target Variance],<br>    [Sales Target]<br>)</pre><p>The intention is immediately obvious.</p><p>Microsoft specifically recommends DIVIDE() over manually testing the denominator because it is better optimized for this purpose and results in a more concise expression.</p><p>This does not mean IF() is bad.</p><p>IF() is essential when the business logic genuinely contains several branches.</p><p>But this:</p><pre>IF denominator invalid<br>then BLANK<br>else divide</pre><p>is exactly the problem DIVIDE() already solves.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LOg1DaElZLAcALjYvCZlXQ.png" /></figure><h3>When should you still use “/”?</h3><p>This article is not an argument for replacing every slash in your model.</p><p>Consider:</p><pre>Sales in Thousands :=<br>[Sales] / 1000</pre><p>The denominator:</p><pre>1000</pre><p>cannot become zero.</p><p>A filter cannot turn it into blank.</p><p>A slicer cannot change it.</p><p>There is no need for defensive denominator handling.</p><p>Microsoft recommends the division operator for constant denominators for exactly this reason.</p><p>Other examples:</p><pre>Sales in Millions :=<br>[Sales] / 1000000</pre><pre>Daily Rate :=<br>[Annual Rate] / 365</pre><p>assuming your business definition genuinely treats 365 as the fixed divisor.</p><pre>Percentage :=<br>[Decimal Value] * 100</pre><p>No DIVIDE() is required simply because division exists somewhere conceptually.</p><p>The decision depends on the denominator.</p><h3>The four-question test</h3><p>Before choosing between / and DIVIDE(), ask four questions.</p><h3>1. Can the denominator change with filter context?</h3><p>Example:</p><pre>[Profit] / [Sales]</pre><p>Yes.</p><p>[Sales] depends on context.</p><p>Review it.</p><h3>2. Can it become zero or BLANK?</h3><p>If yes, DIVIDE() is usually the safer option.</p><h3>3. What should an invalid calculation mean?</h3><p>Should the result be:</p><pre>BLANK</pre><p>or:</p><pre>0</pre><p>or some other fixed business fallback?</p><p>That is not merely a technical choice.</p><p>It changes the meaning of the report.</p><h3>4. Is the denominator a guaranteed non-zero constant?</h3><p>If yes:</p><pre>[Sales] / 1000</pre><p>is perfectly reasonable.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0Jy1p4ge1VknZTnc5w0pvQ.png" /></figure><h3>What most developers get wrong</h3><h3>“DIVIDE is always better than slash.”</h3><p>No.</p><p>DIVIDE() is recommended when the denominator is dynamic and may return zero or blank.</p><p>For a constant denominator, Microsoft recommends /.</p><h3>“If I use DIVIDE, I should always provide 0 as the third argument.”</h3><p>No.</p><p>The default BLANK is often semantically better and can produce more efficient report visuals.</p><h3>“BLANK and zero are basically the same in a visual.”</h3><p>No.</p><p>They communicate different information.</p><pre>BLANK = no meaningful result<br>0     = meaningful result equal to zero</pre><h3>“No data and denominator zero are the same situation.”</h3><p>No.</p><p>Our example demonstrates the difference:</p><pre>East<br>fact exists<br>target = 0</pre><p>versus:</p><pre>Central<br>no matching fact row</pre><h3>“If I want Central to appear, I have to return zero.”</h3><p>No.</p><p>Power BI provides <strong>Show items with no data</strong> for scenarios where the category should be displayed despite no measure data.</p><h3>“A manual IF check is more professional.”</h3><p>Usually not for simple safe division.</p><p>DIVIDE() exists specifically to solve this problem.</p><h3>“Infinity is the only bad outcome.”</h3><p>No.</p><p>A mathematically safe result can still be semantically wrong.</p><p>Returning 0% for a category with no data can be more misleading than displaying an obvious invalid result.</p><h3>Enterprise DAX division checklist</h3><p>Before approving a ratio measure, review the following.</p><h3>Denominator</h3><ul><li>Is it a constant?</li><li>Is it a measure?</li><li>Does it depend on slicers?</li><li>Can filtering reduce it to zero?</li><li>Can it return BLANK?</li><li>Can the underlying fact table have no matching rows?</li></ul><h3>Semantics</h3><ul><li>What does zero mean?</li><li>What does blank mean?</li><li>Is the percentage mathematically meaningful when the denominator is zero?</li><li>Would returning zero mislead the user?</li><li>Is there a genuine business fallback?</li></ul><h3>Implementation</h3><ul><li>Can / safely be used?</li><li>Should DIVIDE() be used?</li><li>Is a manual IF() adding unnecessary complexity?</li><li>Is the alternate result a constant?</li><li>Are blanks being converted only where required?</li></ul><h3>Visual behavior</h3><ul><li>Do empty categories need to be shown?</li><li>Could <strong>Show items with no data</strong> solve the visual requirement?</li><li>Will converting blanks to zero create large dense result sets?</li><li>Does the visual still communicate “no data” correctly?</li></ul><h3>Testing</h3><p>Always test at least:</p><pre>Normal denominator<br>Zero denominator<br>BLANK denominator<br>No fact rows<br>Total<br>Filtered subtotal</pre><p>A measure that works for the normal case is not finished.</p><h3>A better code-review rule</h3><p>Instead of searching your model for / and labeling every occurrence as a mistake, classify each division.</p><h3>Candidate 1</h3><pre>Margin % :=<br>[Profit] / [Sales]</pre><p>Dynamic denominator.</p><p>Review.</p><h3>Candidate 2</h3><pre>Sales K :=<br>[Sales] / 1000</pre><p>Constant denominator.</p><p>Likely fine.</p><h3>Candidate 3</h3><pre>Return Rate :=<br>DIVIDE (<br>    [Returns],<br>    [Sales]<br>)</pre><p>Dynamic denominator with safe handling.</p><p>Likely intentional.</p><h3>Candidate 4</h3><pre>Return Rate :=<br>DIVIDE (<br>    [Returns],<br>    [Sales],<br>    0<br>)</pre><p>Technically safe.</p><p>But ask:</p><blockquote><em>Is zero really the correct business result?</em></blockquote><p>This is why automated DAX review should produce <strong>candidates</strong>, not simplistic error lists.</p><h3>Auditing large measure libraries</h3><p>In a small report, you can inspect these expressions manually.</p><p>In an enterprise model, you might have:</p><pre>200 measures<br>500 measures<br>1,000+ measures</pre><p>including:</p><ul><li>Margin %</li><li>Conversion Rate</li><li>Fill Rate</li><li>Stock Cover</li><li>Return Rate</li><li>Target Achievement</li><li>Average Price</li><li>Cancellation %</li><li>DIFOT %</li><li>Discount %</li></ul><p>Searching every expression manually is time-consuming.</p><p>An automated audit can identify formulas containing the division operator and help classify them for review.</p><p>The important distinction is:</p><pre>FOUND /</pre><p>does not mean:</p><pre>ERROR</pre><p>It means:</p><pre>REVIEW THE DENOMINATOR</pre><p>That is the useful level of automation.</p><h3>Watch the full example</h3><p>In the video version of this article, I walk through the complete Power BI example:</p><ul><li>East with a zero denominator,</li><li>Central with no fact rows,</li><li>/ versus DIVIDE(),</li><li>BLANK versus zero,</li><li>COALESCE,</li><li>and the decision process for choosing the correct pattern.</li></ul><iframe src="https://cdn.embedly.com/widgets/media.html?url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dd6_vlAd2-L0&amp;type=text%2Fhtml&amp;schema=youtube&amp;display_name=YouTube&amp;src=https%3A%2F%2Fwww.youtube.com%2Fembed%2Fd6_vlAd2-L0" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/b3ee8400970c79768bc9723b495d865f/href">https://medium.com/media/b3ee8400970c79768bc9723b495d865f/href</a></iframe><h3>Audit DAX patterns with ModelLens</h3><p>Finding one risky division is easy.</p><p>Reviewing hundreds of measures is not.</p><p>ModelLens can help surface DAX patterns that deserve attention and give you a focused list of measures to inspect.</p><p>The goal is not to blindly replace:</p><pre>/</pre><p>with:</p><pre>DIVIDE()</pre><p>The goal is to answer:</p><blockquote><em>What is the denominator?</em></blockquote><blockquote><em>Can it become zero?</em></blockquote><blockquote><em>Can it become blank?</em></blockquote><blockquote><em>What should the business result be?</em></blockquote><blockquote><em>Is this division actually safe?</em></blockquote><p>That is the difference between a syntax cleanup and a real model audit.</p><p><strong>ModelLens:</strong><br><a href="https://thebitoolbox.gumroad.com/l/modellens">ModelLens: Automated Power BI Semantic Model Auditor &amp; Documentation</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ry6Ze7TUWoo0NETFWM0cSg.png" /><figcaption><strong><em>The operator is not the problem. The denominator is.</em></strong></figcaption></figure><h3>Final rule</h3><p>Do not memorize:</p><pre>/ = bad<br>DIVIDE = good</pre><p>Memorize this instead:</p><pre>DYNAMIC DENOMINATOR<br>        ↓<br>    DIVIDE()</pre><pre>SAFE CONSTANT<br>        ↓<br>        /</pre><p>And separately:</p><pre>BLANK ≠ 0</pre><p>The operator is not the real mistake.</p><p><strong>Ignoring what the denominator can become is.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=15031c6b3f40" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/divide-vs-in-dax-how-to-handle-zero-and-blank-denominators-safely-15031c6b3f40">DIVIDE vs “/” in DAX: How to Handle Zero and BLANK Denominators Safely</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Safely Delete Power BI Columns Without Breaking Your Model]]></title>
            <link>https://levelup.gitconnected.com/how-to-safely-delete-power-bi-columns-without-breaking-your-model-56f00cbeac53?source=rss-dd2443488cef------2</link>
            <guid isPermaLink="false">https://medium.com/p/56f00cbeac53</guid>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[optimization]]></category>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Thebitoolbox]]></dc:creator>
            <pubDate>Fri, 07 Aug 2026 15:40:57 GMT</pubDate>
            <atom:updated>2026-08-07T15:40:57.714Z</atom:updated>
            <content:encoded><![CDATA[<h4>The column looks useless. Your cursor is already over Delete. Then doubt appears.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6dKNsy44UhIuaRqsgPltjA.png" /><figcaption>Generated with ChatGPT</figcaption></figure><p>Every Power BI developer eventually inherits a model containing columns like these:</p><ul><li>LegacyCode</li><li>CustomerFax</li><li>MigrationID</li><li>OldCategory</li><li>InternalFlag</li><li>ArchiveReference</li><li>TemporaryStatus</li></ul><p>They are not used in any obvious chart.</p><p>Nobody on the team remembers why they were added.</p><p>Some values look as though they came from a system retired years ago.</p><p>You right-click one of the columns.</p><p>Your cursor moves toward <strong>Delete</strong>.</p><p>Then it stops.</p><blockquote><em>What if a measure still depends on it?<br>What if it is used in a relationship?<br>What if removing it breaks row-level security?</em></blockquote><p>That hesitation is reasonable.</p><p>Deleting the wrong column can break calculations, relationships, sorting, security rules, and reports. But keeping every suspicious column “just in case” also has a cost.</p><p>The real question is not:</p><blockquote><strong><em>“Does this column look old?”</em></strong></blockquote><p>It is:</p><blockquote><strong><em>“Can I prove that this column is no longer needed?”</em></strong></blockquote><p>This article explains how to make that decision safely.</p><p>You will learn:</p><ul><li>why “not visible in a report” does not mean unused,</li><li>where column dependencies can hide,</li><li>why unused columns still consume model memory,</li><li>why some columns cost far more than others,</li><li>how to inspect semantic-model dependencies,</li><li>how to measure the actual cost of a column,</li><li>and how to remove candidates without turning cleanup into production damage.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/856/1*6ctkFhPX_L_eLp62VrdKcQ.png" /><figcaption><strong><em>The hardest part is rarely deleting the column. It is proving that the deletion is safe</em></strong></figcaption></figure><h3>“Not used in a visual” does not mean unused</h3><p>Suppose LegacyCode does not appear on any visible report page.</p><p>That tells us only one thing:</p><blockquote>The column is not obviously being displayed in the visuals we checked.</blockquote><p>It does not prove that the column is unused.</p><p>A column can influence the model without ever being placed directly on a chart.</p><p>It might be referenced by:</p><ul><li>a measure,</li><li>a calculated column,</li><li>a calculated table,</li><li>a relationship,</li><li>a hierarchy,</li><li>a <strong>Sort by column</strong> setting,</li><li>a row-level security rule,</li><li>a perspective,</li><li>a report-level filter,</li><li>a hidden visual,</li><li>or another object that depends on it indirectly.</li></ul><p>Power BI’s Model Explorer exposes many semantic-model object types in one place, including tables, measures, relationships, roles, calculation groups, perspectives, and translations. Microsoft also notes that many objects that are not used directly in visuals still affect how the model and reports behave.</p><p>That means a column can look irrelevant in Report view while still performing an important job elsewhere.</p><h3>A practical example</h3><p>Start with a simple measure:</p><pre>Sales Amount :=<br>SUM ( Sales[SalesAmount] )</pre><p>Now create another measure:</p><pre>Legacy Customer Sales :=<br>CALCULATE (<br>    [Sales Amount],<br>    LEFT ( Sales[LegacyCode], 4 ) = &quot;LG-1&quot;<br>)</pre><p>The LegacyCode column does not need to appear in a visual.</p><p>It is still required by the measure.</p><p>The dependency is:</p><pre>Sales[LegacyCode]<br>        ↓<br>Legacy Customer Sales</pre><p>Deleting the column breaks the calculation.</p><p>The same problem can occur with security.</p><p>For example, a role could contain this filter:</p><pre>Sales[InternalFlag] = &quot;Y&quot;</pre><p>InternalFlag might be hidden and absent from every report page.</p><p>It is still part of the security design.</p><p>A technical key presents another common example. A user may never see ProductKey, but it can be essential to the relationship between DimProduct and FactSales.</p><pre>DimProduct[ProductKey]<br>          1<br>          │<br>          *<br>FactSales[ProductKey]</pre><p>Removing the key does not merely clean up the model.</p><p>It removes the relationship.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/947/1*7DJHbv2iS0JT-gRgbd5bDw.png" /><figcaption><strong><em>A column can be invisible to report users and still be essential to the semantic model</em></strong></figcaption></figure><h3>Why unused columns still have a cost</h3><p>Power BI Import models do not query the source for every visual interaction.</p><p>The imported data is loaded into the VertiPaq storage engine.</p><p>VertiPaq creates internal storage structures separately for each column. Numeric columns can use value encoding, while text and other nonnumeric columns commonly require hash encoding, where unique values are assigned identifiers and stored in a dictionary.</p><p>That has an important consequence:</p><blockquote><em>A loaded column does not become free because nobody uses it in a chart.</em></blockquote><p>If the column is included in the imported table, it is still processed and stored as part of the model.</p><p>This does not mean every extra column destroys performance.</p><p>A small flag in a table containing a few thousand rows may be practically irrelevant.</p><p>But a wide fact table containing tens of millions of rows can accumulate significant waste when it includes dozens of unnecessary columns.</p><p>Microsoft explicitly recommends reducing the number of imported columns and reviewing high-cardinality columns as part of semantic-model optimization.</p><p>Unused columns can contribute to:</p><ul><li>larger in-memory models,</li><li>higher refresh resource requirements,</li><li>longer processing,</li><li>increased capacity memory pressure,</li><li>more metadata for developers to understand,</li><li>and a more confusing field list.</li></ul><p>The effect depends on the column.</p><p>And this is where an important nuance appears.</p><h3>Not all columns cost the same</h3><p>Compare two columns, each containing one million rows.</p><h3>Column A: a simple flag</h3><pre>InternalFlag<br>Y<br>N<br>Y<br>N<br>Y<br>N<br>...</pre><p>Cardinality:</p><blockquote><strong><em>2 unique values</em></strong></blockquote><h3>Column B: a nearly unique identifier</h3><pre>ArchiveReference</pre><pre>AR-100001<br>AR-100002<br>AR-100003<br>AR-100004<br>...</pre><p>Cardinality:</p><blockquote><strong><em>Almost one unique value per row</em></strong></blockquote><p>Both columns have one million rows.</p><p>Their memory cost can be very different.</p><p>The flag repeats only two values and can compress efficiently.</p><p>The archive identifier requires a much larger set of unique values. Text values use dictionary-style encoding, and Microsoft notes that converting suitable text identifiers to numeric values can produce significant reductions, especially for unique or high-cardinality columns in large tables.</p><p>Therefore, this rule is too simplistic:</p><blockquote><strong><em>“Remove every unused column because every column is equally expensive.”</em></strong></blockquote><p>A better rule is:</p><blockquote><strong><em>Find unused columns, then prioritize them by actual cost.</em></strong></blockquote><p>An unused two-value flag might be safe to remove but have almost no measurable impact.</p><p>An unused, nearly unique text identifier in a 100-million-row fact table may deserve immediate attention.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S5-W3dKNVLEVoS5L8SXg7Q.png" /><figcaption><strong><em>Row count is only part of the story. Cardinality and data type strongly influence storage cost</em></strong></figcaption></figure><h3>Hiding a column is not the same as removing it</h3><p>Power BI allows authors to hide columns from the report field list.</p><p>This is useful for:</p><ul><li>technical relationship keys,</li><li>helper columns,</li><li>fields that should be accessed only through explicit measures,</li><li>or columns that would confuse report authors.</li></ul><p>But hiding is a usability decision.</p><p>It does not remove the underlying data.</p><p>Microsoft describes hiding as a way to control which objects report authors are expected to use. The hidden object remains part of the semantic model.</p><p>Therefore:</p><blockquote><strong><em>HIDDEN ≠ REMOVED</em></strong></blockquote><p>A hidden, high-cardinality column can still consume memory.</p><p>To reduce an Import model, the column must stop being loaded.</p><p>That can happen:</p><ul><li>in the source query,</li><li>in a database view,</li><li>in a dataflow,</li><li>or in Power Query using <strong>Choose columns</strong> or <strong>Remove columns</strong>.</li></ul><p>Power Query’s remove-column operation changes the table passed to subsequent transformation steps and ultimately to the model.</p><h3>Prefer selecting what you need</h3><p>For stable source schemas, this Power Query approach is often safer:</p><pre>Table.SelectColumns (<br>    Source,<br>    {<br>        &quot;SalesID&quot;,<br>        &quot;OrderDate&quot;,<br>        &quot;ProductID&quot;,<br>        &quot;CustomerID&quot;,<br>        &quot;Quantity&quot;,<br>        &quot;SalesAmount&quot;<br>    }<br>)</pre><p>Instead of loading every source column and deleting selected unwanted fields, Table.SelectColumns creates an explicit contract describing what the model needs.</p><p>Another option is:</p><pre>Table.RemoveColumns (<br>    Source,<br>    {<br>        &quot;CustomerFax&quot;,<br>        &quot;ArchiveReference&quot;,<br>        &quot;MigrationID&quot;<br>    }<br>)</pre><p>Both can work.</p><p>Selecting required columns is often easier to review because the approved schema is visible in one place.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vXIMOV15T1unVNWd1jF7BA.png" /><figcaption><strong><em>Hiding cleans the interface. Removing reduces the model</em></strong></figcaption></figure><h3>How can you check whether a column is used?</h3><p>Power BI contains much of the required information, but it does not always present it as one simple answer.</p><p>There is no universal native button that says:</p><blockquote>Where is this column used?</blockquote><p>Instead, the information can be distributed across different parts of the model.</p><h3>Model Explorer</h3><p>Model Explorer can help you navigate:</p><ul><li>tables,</li><li>measures,</li><li>relationships,</li><li>roles,</li><li>calculation groups,</li><li>perspectives,</li><li>translations,</li><li>and other semantic-model objects.</li></ul><p>It is useful for browsing the model.</p><p>However, browsing objects is not the same as automatically producing a full dependency tree for one selected column.</p><h3>Power Query dependencies</h3><p>Power Query can visualize dependencies between queries.</p><p>This helps answer questions such as:</p><blockquote>Which query references this staging query?</blockquote><p>It does not answer every semantic-model question, such as:</p><blockquote>Which DAX measure references this column?</blockquote><p>or:</p><blockquote>Is this column used by an RLS role?</blockquote><p>Power Query dependencies describe query-to-query processing relationships, not the complete dependency graph of the semantic model.</p><h3>DAX metadata functions</h3><p>Current versions of DAX provide metadata functions that can expose object dependencies directly.</p><p>One useful example is:</p><pre>EVALUATE<br>    INFO.DEPENDENCIES()</pre><p>INFO.DEPENDENCIES() returns metadata describing dependencies and relationships between semantic-model objects. Its output can include the object, its type, its expression, and the referenced table and object.</p><p>A simplified result might look like this:</p><pre>OBJECT                  REFERENCED_TABLE    REFERENCED_OBJECT<br>----------------------------------------------------------------<br>Legacy Customer Sales   Sales               LegacyCode<br>Old Category A Sales    Sales               OldCategory</pre><p>You can filter the results:</p><pre>EVALUATE<br>FILTER (<br>    INFO.DEPENDENCIES(),<br>    [REFERENCED_TABLE] = &quot;Sales&quot;<br>        &amp;&amp; [REFERENCED_OBJECT] = &quot;LegacyCode&quot;<br>)</pre><p>This gives you a direct view of semantic-model objects that reference the selected column.</p><p>INFO.DEPENDENCIES() is particularly useful for:</p><ul><li>auditing,</li><li>technical documentation,</li><li>dependency exports,</li><li>impact analysis,</li><li>and building automated model-review processes.</li></ul><p>Microsoft notes that metadata access and available results can depend on the host and permissions.</p><h3>An important limitation</h3><p>A semantic-model dependency query should not automatically be treated as proof that the column is unused everywhere.</p><p>That is an inference from its documented scope: INFO.DEPENDENCIES() describes semantic-model dependencies. External reports, exports, downstream processes, custom applications, or other artifacts may require separate validation.</p><p>Therefore:</p><pre>No detected model dependency</pre><p>means:</p><blockquote><strong><em>Candidate for review</em></strong></blockquote><p>It does not always mean:</p><blockquote><strong><em>Delete immediately</em></strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/889/1*KEKN4T1S4IWZVh_anCVQtQ.png" /><figcaption><strong><em>Dependency metadata turns “I think it is unused” into a question that can be investigated systematically</em></strong></figcaption></figure><h3>Measure the column before prioritizing it</h3><p>Knowing that a column is unused is only half of the decision.</p><p>You also need to know whether removing it is likely to matter.</p><p>DAX Studio’s <strong>View Metrics</strong> feature can analyze model memory and display statistics for tables, columns, relationships, and partitions. Its Column Metrics view presents columns in a flat list, while the Table Metrics view can expand each table to show its columns.</p><p>Typical metrics include:</p><ul><li>cardinality,</li><li>data size,</li><li>dictionary size,</li><li>hierarchy size,</li><li>total size,</li><li>data type,</li><li>encoding information.</li></ul><p>In DAX Studio:</p><pre>Advanced<br>    ↓<br>View Metrics<br>    ↓<br>Columns<br>    ↓<br>Sort by Total Size</pre><p>This allows you to distinguish between:</p><pre>CustomerFax<br>Unused<br>Total size: 0.2 MB</pre><p>and:</p><pre>ArchiveReference<br>Unused<br>Total size: 180 MB</pre><p>Both might be safe candidates.</p><p>Only one is likely to produce a meaningful model-size reduction.</p><h3>Memory size versus file size</h3><p>DAX Studio’s model metrics describe memory used when the model is loaded. The saved file can have a different size because additional compression can be applied on disk.</p><p>Do not expect:</p><pre>Removing a 100 MB in-memory column</pre><p>to reduce the PBIX file by exactly 100 MB.</p><p>The more reliable comparison is:</p><ol><li>capture model metrics before removal,</li><li>remove the column,</li><li>refresh the model,</li><li>capture the same metrics again.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kELFdDriJsLSgNwQGUlV6A.png" /><figcaption><strong><em>Unused tells you whether a column might be removable. Model metrics tell you whether it is worth prioritizing</em></strong></figcaption></figure><h3>Which columns should you remove first?</h3><p>Use two dimensions:</p><ol><li><strong>Usage</strong></li></ol><ul><li>used,</li><li>unused.</li></ul><ol><li><strong>Memory cost</strong></li></ol><ul><li>low,</li><li>high.</li></ul><p>That produces four categories.</p><h3>Used and expensive</h3><p>Example:</p><pre>LegacyCode<br>12.4 MB<br>Used by three measures</pre><p>Decision:</p><pre>REVIEW — DO NOT DELETE</pre><p>This is an optimization candidate, not a deletion candidate.</p><p>Possible actions include:</p><ul><li>changing the data type,</li><li>reducing cardinality,</li><li>replacing text with a numeric key,</li><li>moving logic upstream,</li><li>or redesigning the dependent calculations.</li></ul><h3>Unused and expensive</h3><p>Example:</p><pre>ArchiveReference<br>18.7 MB<br>No detected dependencies</pre><p>Decision:</p><pre>FIRST REMOVAL CANDIDATE</pre><p>This is where cleanup can deliver the greatest return.</p><h3>Used and small</h3><p>Example:</p><pre>InternalFlag<br>0.1 MB<br>Used in RLS</pre><p>Decision:</p><pre>KEEP</pre><p>The storage cost is low, and the column serves an important purpose.</p><h3>Unused and small</h3><p>Example:</p><pre>CustomerFax<br>0.2 MB<br>No detected dependencies</pre><p>Decision:</p><pre>LOW-PRIORITY CANDIDATE</pre><p>It can still be removed to simplify the model.</p><p>But it should not distract you from much larger opportunities.</p><p>The practical prioritization rule is:</p><pre>USED OR UNUSED?<br>        ↓<br>CHEAP OR EXPENSIVE?<br>        ↓<br>PRIORITIZE THE IMPACT<br></pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-QIJ_hqjHEx0k4Hv-qV8bg.png" /><figcaption><strong><em>The best first candidate is not simply unused. It is unused, expensive, and verified as safe to remove</em></strong></figcaption></figure><h3>A safe five-step cleanup process</h3><p>Removing columns should be a controlled model change, not an improvised act of bravery.</p><h3>Step 1: Identify a candidate</h3><p>Start with suspicious columns such as:</p><ul><li>legacy technical fields,</li><li>duplicated descriptions,</li><li>free-text notes,</li><li>timestamps with unnecessary precision,</li><li>GUIDs,</li><li>source-system audit columns,</li><li>migration identifiers,</li><li>fields with no detected usage.</li></ul><p>Do not delete anything yet.</p><p>Create a review list.</p><h3>Step 2: Check dependencies</h3><p>Investigate:</p><ul><li>measures,</li><li>calculated columns,</li><li>calculated tables,</li><li>relationships,</li><li>hierarchies,</li><li>Sort by column settings,</li><li>RLS roles,</li><li>perspectives,</li><li>report visuals,</li><li>page and report filters,</li><li>downstream reports and processes.</li></ul><p>A dependency can be direct:</p><pre>LegacyCode<br>    ↓<br>Legacy Customer Sales</pre><p>or indirect:</p><pre>LegacyCode<br>    ↓<br>Legacy Customer Sales<br>    ↓<br>Executive KPI</pre><p>Both matter.</p><h3>Step 3: Measure the cost</h3><p>Use model metrics to check:</p><ul><li>cardinality,</li><li>dictionary size,</li><li>data size,</li><li>total size,</li><li>and table row count.</li></ul><p>Prioritize columns where:</p><pre>No usage<br>+<br>High memory cost<br>=<br>High-value candidate</pre><h3>Step 4: Remove it in a test copy</h3><p>Do not begin in the only production version of the file.</p><p>Use:</p><ul><li>source control for PBIP projects,</li><li>a copied PBIX,</li><li>a development branch,</li><li>or a separate test workspace.</li></ul><p>Remove the column in the source or Power Query.</p><p>Then perform a full refresh.</p><h3>Step 5: Validate the result</h3><p>Check:</p><ul><li>refresh completion,</li><li>DAX measures,</li><li>calculated objects,</li><li>relationships,</li><li>report pages,</li><li>hidden pages,</li><li>bookmarks,</li><li>drillthrough,</li><li>tooltips,</li><li>RLS roles,</li><li>exported reports,</li><li>downstream connected reports,</li><li>and any automation depending on the model.</li></ul><p>Only after validation should the cleanup move into production.</p><h3>What most developers get wrong</h3><h3>“The column is not in a visual, so it is unused.”</h3><p>False.</p><p>A measure, relationship, sorting rule, security role, or another semantic-model object may still reference it.</p><h3>“Hidden columns do not consume memory.”</h3><p>False.</p><p>Hiding changes field-list visibility. It does not remove the column’s data from the model.</p><h3>“Every unused column is a major performance problem.”</h3><p>False.</p><p>Some columns are tiny and compress extremely well.</p><p>Measure the cost before claiming that removal will transform the report.</p><h3>“All text columns are bad.”</h3><p>False.</p><p>Text columns are often necessary for filtering and reporting.</p><p>The concern is unnecessary text — particularly high-cardinality text in large tables. Microsoft’s optimization guidance focuses on reducing imported columns and reviewing high-cardinality data, not eliminating all descriptive fields.</p><h3>“No DAX dependency means safe to delete.”</h3><p>Not necessarily.</p><p>The column may be involved in a relationship, sorting, RLS, a report visual, or a downstream artifact.</p><p>DAX expressions are only one layer of dependency.</p><h3>“A deletion candidate should be deleted automatically.”</h3><p>No.</p><p>Automated auditing should reduce search effort.</p><p>It should not replace validation.</p><h3>“I can remove it now and fix anything that breaks later.”</h3><p>That can work in a small personal report.</p><p>It is a dangerous cleanup strategy for a shared enterprise semantic model.</p><h3>Enterprise unused-column checklist</h3><h3>Candidate identification</h3><ul><li>Is the column visible in any active report?</li><li>Is it used in page, visual, or report filters?</li><li>Is it used in a tooltip or drillthrough page?</li><li>Is it included in a bookmark-dependent visual?</li><li>Is it used by Analyze in Excel or another downstream report?</li><li>Does the business still require the underlying attribute?</li></ul><h3>Semantic-model dependencies</h3><ul><li>Is it referenced by a measure?</li><li>Is it referenced by a calculated column?</li><li>Is it referenced by a calculated table?</li><li>Does it participate in a relationship?</li><li>Is it used by a hierarchy?</li><li>Is it used as a Sort by column?</li><li>Is it referenced by an RLS role?</li><li>Is it included in a perspective?</li><li>Are there indirect dependencies?</li></ul><h3>Storage impact</h3><ul><li>What is the column’s data type?</li><li>How many rows does it contain?</li><li>What is its cardinality?</li><li>What is its dictionary size?</li><li>What is its total in-memory size?</li><li>Is it located in a large fact table?</li><li>Could the data type be optimized instead of removing it?</li></ul><h3>Removal safety</h3><ul><li>Is the model under source control?</li><li>Is there a test copy?</li><li>Can the model complete a full refresh after removal?</li><li>Have all measures been validated?</li><li>Have relationships been reviewed?</li><li>Has RLS been tested with <strong>View as</strong>?</li><li>Have downstream reports been checked?</li><li>Is there a rollback path?</li></ul><h3>Final decision</h3><ul><li>Is the column unused?</li><li>Is it expensive enough to prioritize?</li><li>Has the absence of dependencies been verified?</li><li>Has the change passed testing?</li></ul><p>Only when all four answers are clear should the column move from:</p><pre>CANDIDATE</pre><p>to:</p><pre>APPROVED FOR REMOVAL</pre><h3>FAQ</h3><h3>Should I remove every unused Power BI column?</h3><p>Removing genuinely unnecessary columns is good model hygiene.</p><p>However, prioritize by impact. Start with wide fact tables and expensive high-cardinality columns rather than spending hours removing tiny fields that save almost no memory.</p><h3>Does removing columns always make visuals faster?</h3><p>Not necessarily.</p><p>The most immediate effect is usually model-size and refresh-resource reduction.</p><p>Query improvement depends on whether the column or its structures were relevant to the queries and memory pressure involved.</p><h3>Are unused columns more important in fact tables?</h3><p>Often, yes.</p><p>Fact tables typically contain far more rows than dimensions, so an unnecessary column repeated across tens of millions of rows can be far more costly than a similar column in a small dimension.</p><p>The actual answer still depends on compression, cardinality, and data type.</p><h3>Should I delete technical key columns?</h3><p>Not when they are required by relationships.</p><p>Technical keys are often hidden from report authors but remain essential to model integrity.</p><h3>Is hiding a column enough?</h3><p>Hiding is enough when the goal is improving usability.</p><p>It is not enough when the goal is reducing imported data or model memory.</p><h3>Can INFO.DEPENDENCIES prove that a column is safe?</h3><p>It can provide valuable semantic-model dependency metadata.</p><p>Treat the output as evidence, not as the entire validation process. Report objects and external dependencies might require separate checks.</p><h3>Can DAX Studio find unused columns automatically?</h3><p>DAX Studio can expose model metrics and metadata that help with the analysis. Its View Metrics feature is particularly useful for understanding memory usage by table and column.</p><p>Determining “unused” still requires combining usage and dependency information.</p><h3>Should I remove the column in Power Query or directly from the source?</h3><p>Removing it at the source is often preferable when you control the source and the field is unnecessary for every consumer.</p><p>Power Query is appropriate when the source is shared or cannot be changed.</p><p>In both cases, the goal is to prevent the unnecessary column from being loaded into the semantic model.</p><h3>The final rule</h3><p>Do not keep every column because deleting it feels risky.</p><p>Do not delete a column because its name looks old.</p><p>Instead:</p><pre>FIND<br>  ↓<br>VERIFY<br>  ↓<br>MEASURE<br>  ↓<br>REMOVE<br>  ↓<br>VALIDATE</pre><p>A clean model is not one with the smallest possible number of columns.</p><p>It is one where every loaded column has a clear purpose — or a justified reason to remain.</p><h3>Watch the visual explanation</h3><p>I created a video showing the exact moment every Power BI developer recognizes:</p><blockquote><em>“Can I delete this column?”</em></blockquote><p>The video demonstrates:</p><ul><li>a column that looks unused but is referenced by DAX,</li><li>the difference between low and high cardinality,</li><li>the memory cost of loaded columns,</li><li>and a safe process for removing candidates.</li></ul><iframe src="https://cdn.embedly.com/widgets/media.html?url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dy2TvPQFD4dk&amp;type=text%2Fhtml&amp;schema=youtube&amp;display_name=YouTube&amp;src=https%3A%2F%2Fwww.youtube.com%2Fembed%2Fy2TvPQFD4dk" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/c2e21338864ee32e2b2ad221780449cc/href">https://medium.com/media/c2e21338864ee32e2b2ad221780449cc/href</a></iframe><h3>Audit unused-column candidates with ModelLens</h3><p>Finding one suspicious column is easy.</p><p>Reviewing hundreds of columns across:</p><ul><li>DAX expressions,</li><li>relationships,</li><li>and indirect dependency chains</li></ul><p>is much more time-consuming.</p><p>ModelLens helps turn semantic-model metadata into a focused audit.</p><p>Instead of manually searching every object, you can identify unused-column candidates and inspect why another apparently unnecessary column must remain.</p><p>An audit does not make the final deletion decision.</p><p>It gives you the evidence needed to make that decision faster.</p><p><strong>ModelLens:</strong><br><a href="https://thebitoolbox.gumroad.com/l/modellens">ModelLens: Automated Power BI Semantic Model Auditor &amp; Documentation</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/591/1*UZHGU5Qex1EQRufeT9Ftdw.png" /><figcaption><strong><em>Automation should identify candidates and explain dependencies — not blindly delete model objects</em></strong></figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=56f00cbeac53" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/how-to-safely-delete-power-bi-columns-without-breaking-your-model-56f00cbeac53">How to Safely Delete Power BI Columns Without Breaking Your Model</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>