Differences between a calculated column and a measure
The fundamental difference between a calculated column and a measure is that a calculated column creates a value for each row in a table. For example, if the table has 1,000 rows, it will have 1,000 values in the calculated column. Calculated column values are stored in the Power BI .pbix file. Each calculated column will increase the space that is used in that file and potentially increase the refresh time.
Measures are calculated on demand. Power BI calculates the correct value when the user requests it. When you previously dragged the Total Sales measure onto the report, Power BI calculated the correct total and displayed the visual. Measures do not add to the overall disk space of the Power BI .pbix file.
Measures are calculated based on the filters that are used by the report user. These filters combine to create the filter context.
https://learn.microsoft.com/en-us/training/modules/create-measures-dax-power-bi/1-introduction
To create this measure for Sales by Ship Date, you can use the DAX function USERELATIONSHIP(). This function is used to specify a relationship to be used in a specific calculation and is done without overriding any existing relationships. It is a beneficial feature in that it allows developers to make additional calculations on inactive relationships by overriding the default active relationship between two tables in a DAX expression, as shown in the following example:
Sales by Ship Date = CALCULATE(Sum(Sales[TotalPrice]), USERELATIONSHIP(Sales[ShipDate],'Calendar'[Date]))
====================================================================
Work with DAX functions
However, many functions exist that you won't find in Excel because they're specific to data modeling:
- Relationship navigation functions
- Filter context modification functions
- Iterator functions
- Time intelligence functions
- Path functions
====================================================================
Learn about row context
However, row context doesn't extend beyond the table. If your formula needs to reference columns in other tables, you have two options:
- If the tables are related, directly or indirectly, you can use the
RELATED
or RELATEDTABLE
DAX function. The RELATED
function retrieves the value at the one-side of the relationship, while the RELATEDTABLE
retrieves values on the many-side. The RELATEDTABLE
function returns a table object. - When the tables aren't related, you can use the
LOOKUPVALUE
DAX function.
Sales[Order Quantity]
* RELATED('Product'[List Price]) ) - Sales[Sales Amount]
No comments:
Post a Comment