Vlog 5 | Mata Kuliah Business Intelligent | Shifa Aulia Maharani | Akuntansi Bisnis Digital 4D

Summary

In this vlog, Sipaim Hari introduces the concept of DEX (Data Analysis Expressions) in Power BI, explaining its role in interacting with data. He outlines two main types of DEX: the 'calculated column', which performs row-by-row calculations, and 'measure', which calculates data aggregates for visual representation. Sipaim provides a step-by-step demonstration of how to use DEX in Power BI, including formulas for calculating sales, profit, and other data metrics. He also discusses the ease of using Power BI compared to Power Apps. The vlog concludes with an invitation to explore further DEX applications.

Takeaways

• 😀 DEX (Data Analysis Expressions) is a language developed by Microsoft for interacting with data, typically in Power BI.
• 😀 DEX formulas used in Power BI are similar to those in Excel, including functions like SUM, MAX, and others.
• 😀 There are different types of DEX formulas: calculated columns and measures, each serving distinct purposes.
• 😀 A calculated column performs row-by-row calculations and stores the results in a new column in the data model.
• 😀 A measure calculates aggregates without saving results to a new column, and is used for KPIs and other aggregate calculations.
• 😀 In Power BI, using DEX can be simpler compared to other applications like Power Apps, making it user-friendly.
• 😀 To use DEX in Power BI, you first need to open the data model and set up your formulas within the 'New Measure' option.
• 😀 When creating formulas, it's important to define the data you want to aggregate, such as sales or profit, using appropriate functions.
• 😀 The sum of sales and profit can be visualized easily in Power BI using a visualization card after defining the formula correctly.
• 😀 Once the formulas are set up and the data is processed, users can display calculated values such as total sales and profit in their reports.

Q & A

What is Data Analysis Expression (DEX)?

DEX, or Data Analysis Expression, is a language developed by Microsoft used for interacting with data in Power BI. It allows you to perform calculations and aggregations, similar to the formulas used in Microsoft Excel.

What are the two main types of DEX functions in Power BI?

The two main types of DEX functions are 'Calculated Column' and 'Measure'. A calculated column performs row-by-row calculations and saves the result in a new column, while a measure aggregates data without row-level calculations, and the result is shown directly in visuals.

How does a calculated column differ from a measure in DEX?

A calculated column performs calculations on a row-by-row basis and saves the result in a new column, which can be used for filtering or categorizing data. In contrast, a measure aggregates data (e.g., SUM, AVERAGE) and doesn't create new columns; instead, it provides results directly in visuals.

What is the purpose of using a measure in Power BI?

A measure in Power BI is used to perform aggregate calculations such as summing sales, calculating KPIs, or comparing values across periods. Measures provide dynamic results based on the visual context, without storing the values in columns.

Can you give an example of using a calculated column in Power BI?

An example of using a calculated column is creating a new category based on a condition, such as marking 'High Sales' or 'Low Sales' for each row based on the sales amount. This would be done using a formula like 'IF(Sales > 1000, 'High Sales', 'Low Sales').

What DEX formula was used to calculate total sales in the example?

The formula used to calculate total sales in the example was: `Sales = SUM(Orders[SalesAmount])`. This calculates the sum of the 'SalesAmount' column from the 'Orders' table.

How can you visualize total sales in Power BI after creating the measure?

To visualize total sales in Power BI, you can drag the newly created 'Sales' measure into the 'Values' field of a card visual. This will display the total sales, such as 2.30 billion.

What function is used to calculate both sales and profits together in Power BI?

The `SUMX` function is used to calculate both sales and profits together in Power BI. The formula for this would be: `SalesProfit = SUMX(Orders, Orders[SalesAmount] + Orders[Profit])`.

Why is the SUMX function useful in Power BI?

The `SUMX` function is useful for performing row-wise calculations and aggregations in Power BI. It allows you to sum over an expression, which could involve adding multiple fields (e.g., sales and profits) within a table.

How does Power BI handle different types of DEX functions in terms of data storage?

In Power BI, calculated columns store their results directly in the dataset as new columns, while measures do not store any data. Measures provide dynamic results that are recalculated based on the current context of the visual, without affecting the underlying data model.