Ravindra BagaleCourses & study guides मराठी Track your progress

Ravi Sir's Cards › Power BI

13. DAX: Data Analysis Expressions: Cards

मराठी cards · Open the chapter

Revision cards

What is DAX?. Charts need numbers that react to slicers, like revenue for the selected city.
Revision card 1 of 27: What is DAX? · Download PNG · Open card page

Lesson: 13.1 What is DAX?

Calculated columns vs measures: recap. Most DAX mistakes start with choosing the wrong one.
Revision card 2 of 27: Calculated columns vs measures: recap · Download PNG · Open card page

Lesson: 13.2 Calculated Columns vs Measures (Recap)

Evaluation context: filter and row context. The same measure shows different numbers in different cells. Context explains why.
Revision card 3 of 27: Evaluation context: filter and row context · Download PNG · Open card page

Lesson: 13.3 Filter Context and Row Context in Power BI with Example

DAX syntax. Clear syntax makes formulas easy to read and fix.
Revision card 4 of 27: DAX syntax · Download PNG · Open card page

Lesson: 13.4 DAX Syntax

Base measures for the quick-commerce model. Every report needs the same core numbers. Writing them once avoids differences.
Revision card 5 of 27: Base measures for the quick-commerce model · Download PNG · Open card page

Lesson: 13.5 Base Measures in Power BI DAX with Example

Aggregation functions. Each aggregation answers a different question. Picking the wrong one gives a wrong number.
Revision card 6 of 27: Aggregation functions · Download PNG · Open card page

Lesson: 13.6 SUM, SUMX and Other DAX Aggregation Functions with Example

Logical functions: IF, SWITCH, AND, OR. Reports need labels and choices based on rules.
Revision card 7 of 27: Logical functions: IF, SWITCH, AND, OR · Download PNG · Open card page

Lesson: 13.7 IF, SWITCH and DIVIDE in Power BI DAX with Example

CALCULATE: change the filter inside a measure. A measure is a formula that gives one number, like total revenue. Sometimes you need that number for one fixed city, whatever the user clicks.
Revision card 8 of 27: CALCULATE: change the filter inside a measure · Download PNG · Open card page

Lesson: 13.8 CALCULATE in Power BI with Example

FILTER. Some conditions cannot be written as a simple column = value filter.
Revision card 9 of 27: FILTER · Download PNG · Open card page

Lesson: 13.9 FILTER Function in Power BI DAX with Example

ALL, ALLEXCEPT, REMOVEFILTERS, ALLSELECTED. Percent of total needs a total that ignores some filters.
Revision card 10 of 27: ALL, ALLEXCEPT, REMOVEFILTERS, ALLSELECTED · Download PNG · Open card page

Lesson: 13.10 ALL, ALLEXCEPT, REMOVEFILTERS and ALLSELECTED in Power BI with Example

RELATED and RELATEDTABLE. Calculated columns often need values from a linked table.
Revision card 11 of 27: RELATED and RELATEDTABLE · Download PNG · Open card page

Lesson: 13.11 RELATED and RELATEDTABLE in Power BI DAX with Example

USERELATIONSHIP. Only one relationship between two tables can be active, but you need both dates.
Revision card 12 of 27: USERELATIONSHIP · Download PNG · Open card page

Lesson: 13.12 USERELATIONSHIP in Power BI DAX with Example

VALUES, SELECTEDVALUE, HASONEVALUE. Titles and labels should say what the user picked.
Revision card 13 of 27: VALUES, SELECTEDVALUE, HASONEVALUE · Download PNG · Open card page

Lesson: 13.13 VALUES, SELECTEDVALUE and HASONEVALUE in Power BI with Example

Text functions. Labels and titles need clean, joined or formatted text.
Revision card 14 of 27: Text functions · Download PNG · Open card page

Lesson: 13.14 Text Functions

Date functions. Reports often need parts of dates or gaps between dates.
Revision card 15 of 27: Date functions · Download PNG · Open card page

Lesson: 13.15 Date Functions

Time intelligence. Managers compare with last year and the year so far.
Revision card 16 of 27: Time intelligence · Download PNG · Open card page

Lesson: 13.16 Time Intelligence in Power BI with Example: FYTD, Last Year and YoY %

Ranking: RANKX and TOPN. Users want to see the best and worst performers.
Revision card 17 of 27: Ranking: RANKX and TOPN · Download PNG · Open card page

Lesson: 13.17 RANKX and TOPN in Power BI DAX with Example

Variables: VAR and RETURN. Long formulas repeat the same parts and are hard to read and slow.
Revision card 18 of 27: Variables: VAR and RETURN · Download PNG · Open card page

Lesson: 13.18 VAR and RETURN in Power BI DAX with Example

Other useful functions: DIVIDE, COALESCE, BLANK. Dividing by zero or missing values gives errors and ugly visuals.
Revision card 19 of 27: Other useful functions: DIVIDE, COALESCE, BLANK · Download PNG · Open card page

Lesson: 13.19 Other Useful Functions

PREVIOUSMONTH, DATESMTD and rolling totals. Managers ask for last month, month to date and rolling 3 months.
Revision card 20 of 27: PREVIOUSMONTH, DATESMTD and rolling totals · Download PNG · Open card page

Lesson: 13.20 PREVIOUSMONTH, PREVIOUSYEAR, DATESMTD and Rolling 30 and 90 Days

KEEPFILTERS: how a slicer meets a measure. Sometimes you want the measure to respect the slicer, not override it.
Revision card 21 of 27: KEEPFILTERS: how a slicer meets a measure · Download PNG · Open card page

Lesson: 13.21 KEEPFILTERS in Power BI: How a Slicer Hits a Measure, with Example

LOOKUPVALUE, TREATAS and CROSSFILTER. Some tables cannot or should not have a normal relationship.
Revision card 22 of 27: LOOKUPVALUE, TREATAS and CROSSFILTER · Download PNG · Open card page

Lesson: 13.22 LOOKUPVALUE, TREATAS and CROSSFILTER

Virtual tables. Some answers need a temporary table inside a measure.
Revision card 23 of 27: Virtual tables · Download PNG · Open card page

Lesson: 13.23 Virtual Tables

Weighted averages and FILTER inside an iterator. A simple average of prices (₹315) is misleading when quantities differ.
Revision card 24 of 27: Weighted averages and FILTER inside an iterator · Download PNG · Open card page

Lesson: 13.24 Weighted Averages and FILTER inside an Iterator

IFERROR, BLANK versus zero, circular dependency. Errors, blanks and zeros look similar but change what the report shows.
Revision card 25 of 27: IFERROR, BLANK versus zero, circular dependency · Download PNG · Open card page

Lesson: 13.25 IFERROR, Blank versus Zero, and Circular Dependency

Dynamic segmentation, Pareto and ABC. A few products bring most revenue. Managers need to know which.
Revision card 26 of 27: Dynamic segmentation, Pareto and ABC · Download PNG · Open card page

Lesson: 13.26 Dynamic Segmentation, Pareto and ABC

DAX query view. Building a visual just to check a number is slow.
Revision card 27 of 27: DAX query view · Download PNG · Open card page

Lesson: 13.27 DAX Query View

Interview cards

What is filter context in Power BI? Filter context is the set of filters active when a measure is computed. They come from slicers, the Filters pane, and the row or column of the visual.
Interview card 1 of 6: What is filter context in Power BI? · Download PNG · Open card page

Lesson: 13.3 Filter Context and Row Context in Power BI with Example

Why use DISTINCTCOUNT to count orders? In most sales tables, one order has many rows, one per item. COUNT counts rows. DISTINCTCOUNT counts each order ID only once.
Interview card 2 of 6: Why use DISTINCTCOUNT to count orders? · Download PNG · Open card page

Lesson: 13.5 Base Measures in Power BI DAX with Example

What is the difference between SUM and SUMX? SUM adds up one column. SUMX is an iterator: it goes row by row through a table, computes an expression for each row, then adds the results.
Interview card 3 of 6: What is the difference between SUM and SUMX? · Download PNG · Open card page

Lesson: 13.6 SUM, SUMX and Other DAX Aggregation Functions with Example

Why use variables (VAR) in DAX? Variables store a result once and reuse it. They make formulas easier to read, faster (no repeated work) and easier to debug.
Interview card 4 of 6: Why use variables (VAR) in DAX? · Download PNG · Open card page

Lesson: 13.18 VAR and RETURN in Power BI DAX with Example

What does KEEPFILTERS do in CALCULATE? By default, a CALCULATE filter on a column replaces any existing filter on that same column. KEEPFILTERS keeps the existing filter and intersects it with the new one.
Interview card 5 of 6: What does KEEPFILTERS do in CALCULATE? · Download PNG · Open card page

Lesson: 13.21 KEEPFILTERS in Power BI: How a Slicer Hits a Measure, with Example

Should a measure return BLANK or 0 when there is no data? Usually BLANK. Visuals hide rows where every measure is blank, so tables stay short. Returning 0 shows every combination, which can create huge tables and slow reports.
Interview card 6 of 6: Should a measure return BLANK or 0 when there is no data? · Download PNG · Open card page

Lesson: 13.25 IFERROR, Blank versus Zero, and Circular Dependency

Comparison cards

ALL vs ALLSELECTED. Both remove filters inside CALCULATE to get a bigger total, often for percentages.
Comparison card 1 of 4: ALL vs ALLSELECTED · Download PNG · Open card page

Lesson: 13.10 ALL, ALLEXCEPT, REMOVEFILTERS and ALLSELECTED in Power BI with Example

RELATED vs LOOKUPVALUE. Both fetch a value from another table into a row. RELATED uses an existing relationship. LOOKUPVALUE matches columns you name, with no relationship needed.
Comparison card 2 of 4: RELATED vs LOOKUPVALUE · Download PNG · Open card page

Lesson: 13.11 RELATED and RELATEDTABLE in Power BI DAX with Example

FILTER() vs a simple column filter in CALCULATE. Both limit the rows a measure uses. A column filter is written as Column = value inside CALCULATE. FILTER() scans a table row by row with any condition.
Comparison card 3 of 4: FILTER() vs a simple column filter in CALCULATE · Download PNG · Open card page

Lesson: 13.9 FILTER Function in Power BI DAX with Example

SEARCH vs FIND in DAX. Both return the position of one text inside another.
Comparison card 4 of 4: SEARCH vs FIND in DAX · Download PNG · Open card page

Lesson: 13.14 Text Functions