Monday, September 19, 2022

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




          No comments:

          Post a Comment

          Related Posts Plugin for WordPress, Blogger...