Calculated columns and measures

 Measure DAX

Numeric columns support the greatest range of aggregation functions:

• Sum
• Average
• Minimum
• Maximum
• Count (Distinct)
• Count
• Standard deviation
• Variance
• Median

Summarize non-numeric columns

Non-numeric columns can be summarized. However, the sigma symbol does not show next to non-numeric columns in the Fields pane because they don't summarize by default.

Text columns allow the following aggregations:

• First (alphabetically)
• Last (alphabetically)
• Count (Distinct)
• Count

Date columns allow the following aggregations:

• Earliest
• Latest
• Count (Distinct)
• Count

Boolean columns allow the following aggregations:

• Count (Distinct)
• Count

=================================================

Compare calculated columns with measures

Completed100 XP

• 1 minute

DAX beginners often experience a degree of confusion about calculated columns and measures. The following section reviews the similarities and differences between both.

Regarding similarities between calculated columns and measures, both are:

• Calculations that you can add to your data model.
• Defined by using a DAX formula.
• Referenced in DAX formulas by enclosing their names within square brackets.

The areas where calculated columns and measures differ include:

• Purpose - Calculated columns extend a table with a new column, while measures define how to summarize model data.
• Evaluation - Calculated columns are evaluated by using row context at data refresh time, while measures are evaluated by using filter context at query time. Filter context is introduced in a later module; it's an important topic to understand and master so that you can achieve complex summarizations.
• Storage - Calculated columns (in Import storage mode tables) store a value for each row in the table, but a measure never stores values in the model.
• Visual use - Calculated columns (like any column) can be used to filter, group, or summarize (as an implicit measure), whereas measures are designed to summarize.

Resume : Introduction to creating measures using DAX in Power BI

 

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

Use relationships effectively

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.
  
  

Discount Amount =(
    Sales[Order Quantity]
        * RELATED('Product'[List Price]) ) - Sales[Sales Amount]

Gunakan RELATED untuk menJOIN

•