Working with tables and columns in DAX query
In tabular data models, tables may resemble Excel tables, but they differ in their data and formula handling:
Formulas exclusively operate on tables and columns, not individual cells, ranges, or arrays.
Formulas have the capability to utilize relationships to fetch values from connected tables. These retrieved values are always associated with the current row's value.
Irregular or "ragged" data, which is permissible in Excel worksheets, is not allowed in tabular models. Each row in a table must maintain a consistent number of columns, although empty values can exist in certain columns. Excel data tables and tabular model data tables are not interchangeable in this regard.
Each column is assigned a specific data type, necessitating that all values within that column adhere to the same data type.
Tabular data models resemble Excel tables but have distinct rules and functionalities governing how data and formulas are managed.
Referring to tables and columns in formula
You can refer to any table and column by using its name. For example, the following formula illustrates how to refer to columns from two tables by using the fully qualified name:
= SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])
When a formula is evaluated, the model designer first checks for general syntax, and then checks the names of columns and tables that you provide against possible columns and tables in the current context. If the name is ambiguous or if the column or table cannot be found, you will get an error on your formula (an #ERROR string instead of a data value in cells where the error occurs). To learn more about naming requirements for tables, columns, and other objects, see Naming Requirements in DAX syntax.
Creating relationships between tables allows you to utilize related values in calculations. For instance, you can use a calculated column to find all shipping records associated with the current reseller and then sum the shipping costs for each. However, there are cases where a relationship may not be required. In such instances, you can employ the LOOKUPVALUE function to retrieve a value from a specific column based on criteria defined in the search_column and search_value arguments.
Many DAX functions rely on the existence of relationships between tables to locate the referenced columns and produce meaningful results. While some functions attempt to identify relationships, it's advisable to establish relationships whenever possible for optimal outcomes. Tabular data models support multiple relationships among tables. To prevent confusion or incorrect results, one relationship is designated as the active relationship at a time. However, you can modify the active relationship as needed to navigate different data connections in calculations. The USERELATIONSHIP function enables you to specify specific relationships for a particular calculation.
It's crucial to adhere to these formula design principles when using relationships:
When tables are linked by a relationship, ensure that the key columns have matching values. Referential integrity isn't enforced, allowing for non-matching or blank values, which can impact formula results.
Linking tables through relationships expands the context in which your formulas are evaluated. Changes in context due to new tables, relationships, or alterations in the active relationship can lead to unexpected changes in results. For further details, refer to the "Context" section in this article.
Process and Refresh
Understanding the concepts of process and recalculation is essential when dealing with models that involve intricate formulas, large datasets, or data fetched from external sources.
Process or refreshing involves updating a model with fresh data from an external source.
Recalculation entails updating formula results to account for changes in the formulas themselves or alterations in the underlying data. Recalculation can impact performance in the following ways:
Calculated columns: These columns have precomputed values stored in the model. To update these values, you must use one of three processing commands – Process Full, Process Data, or Process Recalc. Whenever you modify the formula, the entire column's results must be recalculated.
Measures: Measure values are dynamically assessed when users employ them in a PivotTable or report. As users adjust the context, measure values adapt accordingly, always reflecting the latest data in the in-memory cache.
It's worth noting that processing and recalculation do not affect row-level security formulas unless the recalculation outcome differs, potentially making the row accessible or inaccessible to role members.
In summary, comprehending these concepts is crucial for effectively managing models with complex formulas and diverse data sources, ensuring accurate and up-to-date results.
DAX undergoes continuous enhancement, regularly introducing fresh and improved functions through monthly updates. The update sequence typically begins with services and then extends to installed applications such as Power BI Desktop, Excel, SQL Server Management Studio (SSMS), and the Analysis Services project extension for Visual Studio (SSDT). SQL Server Analysis Services receives its updates through the subsequent cumulative update. The introduction and details of new functions align with Power BI Desktop updates and are documented in the DAX function reference.
It's important to note that not all functions are compatible with earlier versions of SQL Server Analysis Services and Excel.
When encountering an error while creating a formula, it can fall into one of three categories: syntactic errors, semantic errors, or calculation errors.
Syntactic errors are typically the simplest to resolve and usually involve issues like missing parentheses or commas.
Semantic and calculation errors occur when the formula's syntax is correct, but the values or columns referenced don't fit the context. These errors can be caused by:
Referring to non-existing columns, tables, or functions.
Having the correct syntax, but encountering a type mismatch during data retrieval.
Passing incorrect arguments in terms of number or type to a function.
Referencing a different column with errors, leading to invalid values.
Referring to an unprocessed column with metadata but no actual data for calculations.
In the first four cases, DAX identifies the entire column containing the invalid formula. In the last case, DAX indicates an unprocessed state by graying out the column.
Did you find this article valuable?
Support Sweta_Sarangi by becoming a sponsor. Any amount is appreciated!