Write DAX formulas for Power BI Desktop models Flashcards
What types of calculations can you add to your semantic model using DAX in Power BI?
Calculated tables, calculated columns, and measures.
What is a calculated table in Power BI?
A table created using a DAX formula to duplicate, transform, or generate new data, which is imported into the model and increases storage size.
What is the primary function of a calculated column in Power BI?
To add a column to any table with a DAX formula that evaluates for each row, increasing storage size and evaluated at data refresh for Import mode tables or at query time for DirectQuery tables.
How are measures different from calculated columns in Power BI?
Measures are concerned with summarization, evaluated at query time, and their results are not stored in the model. They are identified with a calculator icon in the Fields pane.
What is a role-playing dimension in Power BI?
A dimension table that plays multiple roles in a model, such as a Date table relating to multiple date columns in another table, using active and inactive relationships.
What is the purpose of What-if parameters in Power BI?
To allow users to select or filter by values in a calculated table, enabling hypothetical analysis without the calculated table propagating filters.
What is the general structure for creating a model calculation in DAX?
<Calculation> = <DAX>
</DAX></Calculation>
What are the main elements used to assemble DAX formulas?
DAX functions, DAX operators, references to model objects, constant values, DAX variables, and whitespace.
What is IntelliSense in Power BI Desktop?
A code-completion aid that lists functions and model resources, helping quickly build accurate formulas.
What must follow function names in DAX formulas?
Parentheses, where variables are passed in.
How should column references be formatted in DAX formulas?
Enclosed within square brackets and optionally preceded by the table name for clarity.
How should measure references be formatted in DAX formulas?
Enclosed within square brackets, and it’s recommended not to precede them with the table name.
What role does whitespace play in DAX formulas?
It helps format the formulas for readability and troubleshooting, without affecting logic or performance.
How can you format a measure for better readability in DAX?
By using spaces, tabs, and carriage returns to structure the formula clearly.
What are the model data types in Power BI?
Whole number, decimal number, boolean, text, date, currency, and BLANK.
How are calculated column data types determined in Power BI?
They are inferred from the formula used.
What is the range of values for a whole number in DAX?
-2^63 through 2^63-1.
How does DAX handle database NULL values?
With the BLANK data type, which represents the absence of a value.
What functions are related to the BLANK data type in DAX?
The BLANK() function returns BLANK, and the ISBLANK() function tests for BLANK.
What is the IF function in DAX used for?
To test a condition and return one value if TRUE and another if FALSE.
Name a few summarization functions available in both Excel and DAX.
SUM, COUNT, AVERAGE, MIN, MAX.
What does the DISTINCTCOUNT function do in DAX?
Counts the number of distinct values in a column.
Why should you use the DIVIDE function in DAX?
It handles division by zero cases and is optimized for performance.
What is a recommended practice when dealing with potential zero or BLANK denominators in DAX?
Use the DIVIDE function for better performance and handling.
