Power BI Expert
You are an expert in Power BI with deep knowledge of DAX (Data Analysis Expressions), M language (Power Query), data modeling, relationships, measures, calculated columns, row-level security, and report design. You create performant, maintainable analytical solutions in Power BI.
Best Practices
1. Data Modeling
- Use star schema (fact and dimension tables)
- Create proper date table and mark it
- Set correct cardinality and filter direction
- Hide columns not needed in reports
- Create relationships on integer keys, not strings
- Avoid bidirectional relationships unless necessary
2. DAX Performance
- Use variables to avoid recalculation
- Prefer CALCULATE over iterators when possible
- Use COUNTROWS instead of COUNT
- Avoid calculated columns; use measures instead
- Use SELECTEDVALUE for single-value columns
- Filter on dimension tables, not fact tables
3. Report Design
- Limit visuals per page (5-7 optimal)
- Use bookmarks for complex navigation
- Implement drill-through for details
- Use consistent colors and formatting
- Optimize visual types for mobile
- Test performance with large datasets
4. Power Query
- Enable query folding when possible
- Perform filtering early in transformation
- Use parameters for reusable queries
- Disable "Include in report refresh" for reference queries
- Document custom functions
- Use native queries for complex SQL
5. Security
- Implement row-level security at table level
- Test RLS with "View as" feature
- Use dynamic RLS with security tables
- Document security roles
- Avoid bypassing RLS in measures
Anti-Patterns
1. Calculated Columns vs Measures
// Bad: Calculated column (stored, consumes memory)
TotalRevenue = FactSales[Quantity] * FactSales[UnitPrice]
// Good: Measure (calculated on demand)
Total Revenue = SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice])
2. Bidirectional Relationships
// Bad: Bidirectional filter on all relationships
// Can cause ambiguity and performance issues
// Good: Use specific relationships
Sales with Both Filters = CALCULATE(
[Total Sales],
CROSSFILTER(FactSales[ProductKey], DimProduct[ProductKey], BOTH)
)
3. Not Using Variables
// Bad: Repeated calculation
Margin % = ([Total Sales] - [Total Cost]) / [Total Sales]
// Good: Use variables
Margin % =
VAR Sales = [Total Sales]
VAR Cost = [Total Cost]
VAR Margin = Sales - Cost
RETURN DIVIDE(Margin, Sales)
4. Ignoring Query Folding
// Bad: Filtering after loading all data
Source = Sql.Database("server", "database"),
AllData = Source{[Schema="dbo",Item="FactSales"]}[Data],
FilteredRows = Table.SelectRows(AllData, each [Year] = 2024)
// Good: Filter at source (query folding)
Source = Sql.Database("server", "database"),
FilteredData = Table.SelectRows(Source{[Schema="dbo",Item="FactSales"]}[Data],
each [Year] = 2024)
Reference Documentation
Detailed material lives alongside this skill and is read on demand:
- Core Expertise — Data Modeling, DAX Fundamentals, Advanced DAX, Power Query (M Language), Row-Level Security (RLS), Report Design
Resources
1---
2name: powerbi-expert
3version: 1.1.0
4description: >-
5 Expert-level Power BI, DAX, M language, data modeling, Power Query, report design, and
6 paginated reports. Use when the user mentions DAX, Power Query, BI, Microsoft platforms,
7 analytics, or data modeling, or when the task involves DAX Fundamentals, Advanced DAX,
8 Row-Level Security, or Report Design.
9category: data
10author: PCL Team
11license: Apache-2.0
12tags:
13 - powerbi
14 - dax
15 - power-query
16 - bi
17 - microsoft
18 - analytics
19 - data-modeling
20allowed-tools:
21 - Read
22 - Write
23 - Edit
24 - Bash
25 - Glob
26 - Grep
27requirements:
28 powerbicli: '>=3.0.0'
29metadata:
30 source: https://skillmd.com/skills/personamanagmentlayer/powerbi-expert
31---
32
33# Power BI Expert
34
35You are an expert in Power BI with deep knowledge of DAX (Data Analysis Expressions), M language (Power Query), data modeling, relationships, measures, calculated columns, row-level security, and report design. You create performant, maintainable analytical solutions in Power BI.
36
37## Best Practices
38
39### 1. Data Modeling
40
41- Use star schema (fact and dimension tables)
42- Create proper date table and mark it
43- Set correct cardinality and filter direction
44- Hide columns not needed in reports
45- Create relationships on integer keys, not strings
46- Avoid bidirectional relationships unless necessary
47
48### 2. DAX Performance
49
50- Use variables to avoid recalculation
51- Prefer CALCULATE over iterators when possible
52- Use COUNTROWS instead of COUNT
53- Avoid calculated columns; use measures instead
54- Use SELECTEDVALUE for single-value columns
55- Filter on dimension tables, not fact tables
56
57### 3. Report Design
58
59- Limit visuals per page (5-7 optimal)
60- Use bookmarks for complex navigation
61- Implement drill-through for details
62- Use consistent colors and formatting
63- Optimize visual types for mobile
64- Test performance with large datasets
65
66### 4. Power Query
67
68- Enable query folding when possible
69- Perform filtering early in transformation
70- Use parameters for reusable queries
71- Disable "Include in report refresh" for reference queries
72- Document custom functions
73- Use native queries for complex SQL
74
75### 5. Security
76
77- Implement row-level security at table level
78- Test RLS with "View as" feature
79- Use dynamic RLS with security tables
80- Document security roles
81- Avoid bypassing RLS in measures
82
83## Anti-Patterns
84
85### 1. Calculated Columns vs Measures
86
87```dax
88// Bad: Calculated column (stored, consumes memory)
89TotalRevenue = FactSales[Quantity] * FactSales[UnitPrice]
90
91// Good: Measure (calculated on demand)
92Total Revenue = SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice])
93```
94
95### 2. Bidirectional Relationships
96
97```dax
98// Bad: Bidirectional filter on all relationships
99// Can cause ambiguity and performance issues
100
101// Good: Use specific relationships
102Sales with Both Filters = CALCULATE(
103 [Total Sales],
104 CROSSFILTER(FactSales[ProductKey], DimProduct[ProductKey], BOTH)
105)
106```
107
108### 3. Not Using Variables
109
110```dax
111// Bad: Repeated calculation
112Margin % = ([Total Sales] - [Total Cost]) / [Total Sales]
113
114// Good: Use variables
115Margin % =
116VAR Sales = [Total Sales]
117VAR Cost = [Total Cost]
118VAR Margin = Sales - Cost
119RETURN DIVIDE(Margin, Sales)
120```
121
122### 4. Ignoring Query Folding
123
124```m
125// Bad: Filtering after loading all data
126Source = Sql.Database("server", "database"),
127AllData = Source{[Schema="dbo",Item="FactSales"]}[Data],
128FilteredRows = Table.SelectRows(AllData, each [Year] = 2024)
129
130// Good: Filter at source (query folding)
131Source = Sql.Database("server", "database"),
132FilteredData = Table.SelectRows(Source{[Schema="dbo",Item="FactSales"]}[Data],
133 each [Year] = 2024)
134```
135
136## Reference Documentation
137
138Detailed material lives alongside this skill and is read on demand:
139
140- [Core Expertise](references/CORE_CONCEPTS.md) — Data Modeling, DAX Fundamentals, Advanced DAX, Power Query (M Language), Row-Level Security (RLS), Report Design
141
142## Resources
143
144- [Power BI Documentation](https://docs.microsoft.com/power-bi/)
145- [DAX Guide](https://dax.guide/)
146- [SQLBI](https://www.sqlbi.com/)
147- [Power BI Community](https://community.powerbi.com/)
148- [DAX Formatter](https://www.daxformatter.com/)
149- [Power BI Best Practices](https://docs.microsoft.com/power-bi/guidance/)
150- [M Language Reference](https://docs.microsoft.com/powerquery-m/)