TIPS, TRICKS & BEST PRACTICES
Power BI Fundamentals | Best Practice | Tips & Tricks
POWER QUERY: Data Transformation
[TRANSFORM DATA AS UPSTREAM AS POSSIBLE & AS DOWNSTREAM AS NECESSARY]
DATA MODEL: BASICS
Best practice for creating a structured Data model:
CONNECTING KEYS:
-TRIM [KEY column] as step 1 when prior to breaking FLAT FILE into FACTS & LOOKUP tables
-REMOVE Blanks from [KEY col] and CHANGE [KEY col] DATA TYPE to type TEXT where possible
DATA MODEL (Transforming FLAT FILE to a DENORMALIZED table structure)
- FLAT FILES- Break into DIMENSION (DIM) & FACT (EXPANDED TABLE)
- Creating STAR SCHEMA Data Model (Easy to Understand, clean data model)
- Design model using WATERFALL METHODOLOGY: Have DIM Tables on top & FACT Table Below
- Use One to Many relationship between DIM (One Side) and FACTS (Many Side) table.
- Use SINGLE "Cross Filter Direction" FROM DIM to FACTS table.
- AVOID bidirectional cross-filter & many to many relationships to avoid ambiguity.
- Simplify model by HIDING tables/ Columns/ Keys in report view which are not required.
RLS TIPS:
-Connecting keys MUST be the same DATA TYPES
-Ensure [Apply Security Filter in Both Directions] is TURNED ON Between PERMISSION & ROLE Table
PRO TIPS:
- Table on the MANY side of the relationship (FACTS Table) automatically become the EXPANDED table
- Aim to spend much time in Power Query, use DAX for aggregations, then visualization
- Avoid adding Physical columns as it increases file size, USE MEASURES instead.
- TURN OFF the Auto-Detect Relationship & Auto Date/Time Feature
- BLANK ROW in Visuals: Investigate missing DATA in DIM table when compared to FACT table
[SAMPLE OF WATERFALL METHODOLOGY FOR A ROBUST MODEL]
DAX: BASICS
CALCULATED COLUMNS: Creates NEW column in a table to describe data better
• Values are calculated based on information from each row of a table (has row context)
• Appends static values to each row in a table and stores them in the model (which increases file size)
• Recalculate on data source refresh or when changes are made to component columns
• Primarily used as rows, columns, slicers or filters
Navigation Function (To ADD Calculated Column)- Used to access field in a RELATED Table
• RELATED(Column Name): Returns a SCALER VALUE from RELATED DIM [One-Side]
• RELATEDTABLE(Table): Returns VALUE from FACTS [Many-Side], used with X-Iterator to return SCALER VAL
CALCULATED MEASURES: Dynamic Aggregations & Time Intelligence Calculations
• Values are calculated based on information from any filters in the report (has filter context)
• Does not create new data in the tables themselves (doesn’t increase file size)
• Recalculate in response to any change to filters within the report
• Almost always used within the values field of a visual
CALCULATED TABLES: New table derived from another table
DEFAULT FILTER CONTEXT IN DAX: Row & Column Headers, Slicers, Page Filters, DAX Formula Filters
[Source: Aaron Parry @Maven]
DAX: KEY CONCEPT SLIDES X 5
1/5
MEASURE BRANCHING METHOLOGY
[Begin from creating CORE Measures & branch out to complex ONES- Great technique to group measures into individual buckets]
PARAMETER & INCREMENTAL REFRESH- SLIDES X 2
1/2
CONNECTIVITY & QUERY FOLDING- SLIDES X 2
1/2
DATAFLOWS ❂ DATASETS
GATEWAYS
WORKSPACE FUNDAMENTALS
ROW LEVEL SECURITY (RLS)
POWER BI: CORE CONCEPTS (Section: Work in Progress)
Copyright © 2022 PowerBI.live - All Rights Reserved. Content for educational purpose only & should not be relied upon as such.
Developed & Managed by Varun Khanna