Power Bi Dax Fundamentals
What is DAX Studio?
Page-level filters apply only to visuals on the current report page, while report-level filters apply to every page in the report. Both are set via the Filters pane and persist across navigation within the report scope.
SELECTEDVALUE returns a column value when it is the only one selected in the filter context; otherwise returns an alternate result (default BLANK). It is shorthand for IF(HASONEVALUE, VALUES, alternate) patterns.
PATH returns a delimited text string of all ancestors of an item in a parent-child hierarchy, starting with the current ID and tracing up to the root. PATHITEM then extracts a specific level from that path.
DAX Studio is a free tool for writing, executing, and analyzing DAX queries against Power BI, Analysis Services, and Power Pivot. It provides query plans, server timings, and diagnostics to optimize measure performance.
Power Bi Dax Fundamentals
What is filter propagation in relationships?
An iterator function evaluates an expression for each row of a table, then aggregates the result. Examples include SUMX, AVERAGEX, MINX, MAXX, COUNTX, and FILTER. They accept a table and an expression as arguments and create row context internally.
COALESCE, available in Power BI, returns the first non-BLANK value among its arguments. It is useful for replacing blanks with alternative values in a single expression, simplifying logic compared to nested IF statements.
Filter propagation means filters applied to one table automatically flow to related tables through active relationships. In a one-to-many relationship, filters flow from the 'one' side to the 'many' side.
ADDCOLUMNS adds calculated columns to a table expression. Syntax: ADDCOLUMNS(table, name1, expression1, ...). It's commonly used with SUMMARIZE for advanced aggregations.
Power Bi Dax Fundamentals
What does CROSSFILTER do in relationships?
IF checks a logical condition and returns one value if TRUE and another if FALSE. Syntax: IF(logical_test, value_if_true, value_if_false). It can also be nested for multiple conditions.
CROSSFILTER modifies the filter direction of a relationship. It can be set to OneDirection, BothDirections, or None. It is often used inside CALCULATE to temporarily change filter propagation.
Use star schema, minimize calculated columns, prefer measures, limit columns and rows imported, set proper data types, disable auto-date tables, and reduce high-cardinality columns and visuals.
Variables (using VAR ... RETURN) let you store intermediate results in a measure. They improve readability, avoid recalculation, and can significantly enhance DAX performance.
Power Bi Dax Fundamentals
What does the ISBLANK function check?
SWITCH evaluates an expression against a list of values and returns a corresponding result. It replaces nested IF statements for multiple conditions. Syntax: SWITCH(expression, value1, result1, ..., else).
ISBLANK returns TRUE if its argument is blank, otherwise FALSE. It's commonly used to handle missing data or to conditionally apply formatting in reports.
CONCATENATEX concatenates the result of an expression evaluated for each row of a table, optionally separated by a delimiter. Useful for dynamic text in card visuals.
Power BI tables can be Import (stored in memory, fastest queries), DirectQuery (queries data source live, no caching), or Dual (storage decided per query). Composite models mix these modes.
Power Bi Dax Fundamentals
What is bookmark usage in Power BI reports?
CALCULATE evaluates an expression in a modified filter context. Arguments passed as filters replace existing filters on specified columns (shadowing). When no filter arguments are provided, it still converts any existing row context into filter context.
Bookmarks capture the current state of a report page, including filters, visuals, and selection. They enable storytelling, dynamic navigation, and custom user experiences when combined with buttons.
A disconnected table has no active relationships to other tables in the model. It is commonly used for parameter selection (e.g., scenarios, what-if analysis) where users pick values that drive DAX measures using SELECTEDVALUE or TREATAS.
A factless fact table contains only keys and no measurable numeric values. It records events or coverage, such as which products were on promotion on which dates, and supports many-to-many scenarios using bridge tables.
Power Bi Dax Fundamentals
What is a waterfall chart used for?
A waterfall chart visualizes cumulative effect of sequential positive and negative values, like breaking down revenue changes from beginning balance, plus gains, minus losses, to ending balance. Requires measures for start, increase, decrease, and total categories.
Use star schema, minimize calculated columns, prefer measures, limit columns and rows imported, set proper data types, disable auto-date tables, and reduce high-cardinality columns and visuals.
SUMMARIZE creates a summary table grouped by specified columns with aggregated expressions. It returns one row per combination of group-by column values.
A slowly changing dimension (SCD) tracks historical changes to dimension attributes over time. Type 1 overwrites, Type 2 keeps history with effective dates, and Type 3 adds previous value columns. Power BI often models Type 2 via separate role-playing dimension tables.
Power Bi Dax Fundamentals
What does DIVIDE do in DAX?
DIVIDE performs division with optional error handling. DIVIDE(numerator, denominator, alternateResult) returns alternateResult when denominator is zero instead of returning #DIV/0!, improving measure robustness and readability.
CLOSINGBALANCEQUARTER evaluates an expression at the last date of the quarter in the current filter context. It is useful for end-of-period snapshots in balance or inventory-related measures across time.
TOPN returns the top N rows of a table based on a sorting expression. Syntax: TOPN(N, table, orderby, [order]). It is commonly used with CALCULATE to compute totals over the top N rows of a category.
A slicer is a visual on-page filter that lets users select values to filter other visuals on the report or dashboard. It supports list, dropdown, between, relative date, and hierarchy styles depending on column data type.