Power BI anti-patterns: 7 silent bugs that cost you CFO trust

1. Why Power BI files break in month 3 (and not month 1)

A Power BI report works. Then someone — usually the CFO or a board member — slices the data in a way the original developer didn't anticipate. Suddenly a measure returns the wrong number, or shows blank, or worse, returns a confidently wrong number.

The root cause is almost always not the visual layer. It's the semantic model underneath. Specifically: the model was built without a clear understanding of filter context, relationship cardinality, or row-level security boundaries.

We audit Power BI files weekly. About 90% have at least one of the seven anti-patterns below. Each one creates a silent bug class — meaning the dashboard 'works' for the use cases the developer tested, but breaks for slicing patterns no one tested.

2. Anti-pattern #1: Bidirectional relationships everywhere

Bidirectional cross-filtering between two tables means filters propagate in both directions. It's seductive — it just works for most ad-hoc questions. But it creates ambiguity in larger models, and the ambiguity manifests as wrong numbers in specific slicing scenarios.

Power BI's filter engine resolves bidirectional relationships using heuristics. When two paths exist between tables (a 'diamond' in the relationship graph), Power BI picks one — often the wrong one for your specific measure.

• Default to single-direction (one-to-many, filtering from the dimension to the fact). 95% of relationships should be this.
• Use bidirectional ONLY when there's a documented reason: many-to-many bridges, role-playing dimensions, or specific cross-filtering requirements.
• Document every bidirectional relationship in a model comments field. Future-you (and the next Power BI dev) will thank you.

// Don't enable bidirectional at the model level.
// Enable it for the specific measure that needs it:

Sales By Customer Country =
CALCULATE(
    SUM(Sales[Amount]),
    CROSSFILTER(Customer[CustomerID], Sales[CustomerID], BOTH)
)

// This contains the bidirectional behavior to ONE measure.
// Other measures still use the single-direction default.

3. Anti-pattern #2: Auto-date hierarchy instead of a proper Date dimension

When you drag a date column to a visual, Power BI auto-creates a hidden date hierarchy (Year > Quarter > Month > Day). This is convenient and almost always wrong. It creates one auto-date table per date column, bloats the file, and breaks fiscal-calendar logic.

The fix: a proper date dimension table. One date table for the whole model, marked as a Date Table, with custom columns for fiscal year, fiscal quarter, working days, and any business-specific attributes.

Date =
ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2030, 12, 31)),
    "Year", YEAR([Date]),
    "Quarter", "Q" & QUARTER([Date]),
    "Month", FORMAT([Date], "mmmm"),
    "Month Number", MONTH([Date]),
    "Year Month", FORMAT([Date], "yyyy-mm"),
    "Weekday", FORMAT([Date], "dddd"),
    "Is Weekend", WEEKDAY([Date], 2) > 5,
    // Fiscal calendar (Apr–Mar)
    "Fiscal Year",
        IF(MONTH([Date]) >= 4,
           "FY" & YEAR([Date]) + 1,
           "FY" & YEAR([Date])),
    "Fiscal Quarter",
        SWITCH(TRUE(),
            MONTH([Date]) IN {4, 5, 6}, "FQ1",
            MONTH([Date]) IN {7, 8, 9}, "FQ2",
            MONTH([Date]) IN {10, 11, 12}, "FQ3",
            "FQ4")
)

// Then in Model view:
// 1. Mark this table as Date Table (right-click > Mark as Date Table)
// 2. Disable auto-date in File > Options > Time intelligence
// 3. Connect every fact table's date column to Date[Date]

4. Anti-pattern #3: SUMX over a fact table without thinking about filter context

The composed-measure approach (Total Revenue = Total Invoiced - Total Credited) is more verbose but bulletproof. Each base measure is independently testable. Changes propagate cleanly. New measures can reuse the bases without re-deriving the logic.

// THIS IS THE BUG WE FOUND IN A CFO DASHBOARD
Revenue Wrong =
SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
// Bug: this includes both invoices AND credit notes,
// because the fact table has both as positive Quantity rows.

// THE FIX: explicit filter
Revenue Correct =
CALCULATE(
    SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]),
    Sales[TransactionType] = "Invoice"
)

// EVEN BETTER: use base measures
Total Revenue =
[Total Invoiced] - [Total Credited]

Total Invoiced =
CALCULATE([Gross Sales], Sales[TransactionType] = "Invoice")

Total Credited =
CALCULATE([Gross Sales], Sales[TransactionType] = "Credit")

Gross Sales =
SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

5. Anti-pattern #4: Row-Level Security that doesn't survive testing

RLS in Power BI uses DAX filter expressions. It looks simple — define a role, write a filter on a dimension table, assign users. The bugs come from two sources: (1) bidirectional relationships propagating filters in unexpected ways, and (2) RLS roles that don't filter the fact table fully.

• Always test RLS with the 'View as' feature in Power BI Desktop. View as the actual user, not just the role definition.
• Test EVERY visual in the report. RLS bugs often hide in summary KPI tiles or rolled-up totals that bypass the dimension filter.
• If you have multiple dimensions filtering RLS (e.g., Region AND Department), use BOTH filters in the same role definition, not two separate roles.
• Document RLS in a tested DAX comment block. Include test cases.

// Role: "Regional Manager"
// Filters Sales fact through Customer dimension by user's assigned region

// On Customer table:
[Region] = LOOKUPVALUE(
    UserAccess[Region],
    UserAccess[UserEmail], USERPRINCIPALNAME()
)

// REQUIREMENT: ensure the relationship Customer -> Sales is single-direction.
// If bidirectional is on, RLS leaks through other tables.

// Test with View As:
//   - Alice (region: NA) → should see only NA customers + their sales
//   - Bob (region: EU) → should see only EU customers + their sales
//   - Both should see correct totals on EVERY visual, including KPI tiles.

6. Anti-pattern #5: Calculated columns where measures belong

Calculated columns evaluate at refresh time and are stored in the model. Measures evaluate at query time and respond to filter context. Choosing the wrong one creates either (a) bloated models that take longer to refresh or (b) measures that return nonsense in pivoted contexts.

// WRONG: calculated column for a percentage
// (this gets the same value for every row, then averages weirdly)
Customer[Pct of Total] =
DIVIDE(
    SUMX(RELATEDTABLE(Sales), Sales[Amount]),
    SUM(Sales[Amount])  // <-- this is wrong; no filter context
)

// RIGHT: measure that responds to filter context
Pct of Total =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALL(Customer))
)

// The measure works correctly in any pivot — by region, by product, by date.
// The calculated column only "works" in specific scenarios.

7. Anti-pattern #6: The 'one big table' (flat denormalized) model

Analysts coming from Excel often build Power BI models as a single flat fact table with everything denormalized. It's easy to start. It scales terribly. Once you have 5M+ rows, query times balloon, model file size explodes, and any change requires re-importing the whole thing.

The right pattern is a star schema: one or more fact tables (transactions, events) connected to dimension tables (Customer, Product, Date, Geography). This is not just a 'best practice' — it's how the VertiPaq engine is designed to work efficiently.

• Fact tables: narrow (only the columns you need to aggregate over), tall (one row per event/transaction), with foreign keys to dimensions.
• Dimension tables: wide (all attributes), short (one row per entity), with primary keys.
• VertiPaq columnar storage compresses dimension columns extremely well — typical compression is 90–98% on category columns. A flat fact table doesn't get this benefit because every row repeats the dimension attributes.

8. Anti-pattern #7: No deployment pipeline (workspace = production)

Most Power BI files we inherit are 'developed in the same workspace where the CFO views them'. Changes go live the moment they're saved. There's no test environment, no version control, no review process.

The fix is Power BI Deployment Pipelines (Power BI Premium / Premium per User). Three workspaces — Dev, Test, Prod. Changes promote through them with explicit deployment steps, not direct edits.

• Dev workspace: where the developer builds. Connected to dev/sample data.
• Test workspace: deployed from Dev. Connected to a representative dataset for UAT testing.
• Prod workspace: deployed from Test after sign-off. The only one users see.
• Source control: store .pbip files (Power BI Project format) in Git. Commits = changes you can roll back.
• ALM Toolkit + Tabular Editor: external tools for diffing models, scripting common changes, and applying CI-style validations.

name: Power BI Model CI
on:
  pull_request:
    paths: ["powerbi/**/*.pbip", "powerbi/**/*.tmdl"]
jobs:
  validate:
    runs-on: windows-latest
    steps:
      - uses: actions/checkout@v4
      - name: Validate model with Tabular Editor CLI
        run: |
          # Run best-practice rules
          tabulareditor.exe powerbi/Model/database.tmdl \
            -A "BestPracticeRules.json" \
            -CL CI \
            -E
        shell: powershell
      - name: Validate measures with DAX rules
        run: |
          # Custom DAX validation: no SUMX on Sales without filter
          # No bidirectional relationships outside the allowed list
          # ...
        shell: powershell

9. The 90-minute Power BI audit checklist

We use this checklist to audit a Power BI file in 90 minutes. About 80% of files reveal at least one bug worth ₹5L+ to the client.

1. 1Check Auto-date hierarchy setting. (File > Options > Data Load > Time intelligence) → should be OFF.
2. 2Verify a custom Date dimension exists, marked as Date Table.
3. 3Inspect every relationship in Model view. Count bidirectional relationships. Document why each one is needed; flag any that aren't.
4. 4Open every measure. Check for SUMX over fact tables without explicit filter context.
5. 5Check for calculated columns that should be measures (look for DIVIDE, SUM with wrong context).
6. 6Verify RLS by 'View as' for at least 2 representative users.
7. 7Check fact-table row count. If under 100K, no concern. If over 5M, audit storage mode (Import / DirectQuery / Direct Lake).
8. 8Check refresh schedule. Failed refreshes in last 7 days?
9. 9Check workspace deployment pipeline. Is there a Test environment?
10. 10Inspect calculation groups (if any). Are time-intelligence calcs centralized or duplicated 30 times?
11. 11Audit data sources. Are credentials current? Any deprecated connectors?
12. 12Open .pbip in source control. Last 5 commits — clear messages? Or 'updated dashboard'?