Power BI Course Tutorial for Beginners

Examples Used in the Chapter : 

Sales2020 = CALCULATE(SUM(Sales[TotalSales]), Sales[Year] = 2020)

Sales2020CategoryA = CALCULATE(

    SUM(Sales[TotalSales]),

    Sales[Year] = 2020,

    Sales[ProductCategory] = "Category A"

)

TotalSalesAllYears = CALCULATE(SUM(Sales[TotalSales]), ALL(Sales[Year]))

HighValueSales = CALCULATE(

    SUM(Sales[TotalSales]),

    FILTER(Sales, Sales[TotalSales] > 1000)

)

TotalSalesSum = SUM(Sales[TotalSales])

TotalRevenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

Summary

• SUM: Adds up all the values in a single column. Use it when you need a simple aggregation of values in one column.

  • Example: SUM(Sales[TotalSales])

• SUMX: Performs row-by-row calculations using an expression and then sums the results. Use it for more complex scenarios where you need to compute values for each row before aggregating.

  • Example: SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

In DAX, the SAMEPERIODLASTYEAR function is used to compare values from the same period in the previous year. This is particularly useful for year-over-year (YoY) analysis, allowing you to compare, for instance, the sales of May 2024 with the sales of May 2023.

DateTable = CALENDAR(DATE(2023, 1, 1), DATE(2024, 12, 31))

In Power BI, the RELATED function in DAX (Data Analysis Expressions) is used to fetch a related value from another table. It works within a row context to retrieve a corresponding value from a related table, which is linked through a relationship.

Here’s a basic explanation and example of how the RELATED function works:

Syntax

dax

RELATED(<column>)

• <column>: The column from the related table that you want to retrieve.

How It Works

1. Relationships: RELATED relies on existing relationships between tables in your data model. There must be a relationship between the table you are working in and the table from which you want to retrieve the data.

2. Row Context: RELATED operates in a row context, meaning it evaluates on a row-by-row basis within the table where it is used.

Example Scenario

Imagine you have two tables:

• Sales: Contains sales transaction data.

  • Columns: OrderID, ProductID, Quantity, SalesAmount

• Products: Contains product details.

  • Columns: ProductID, ProductName, Category, Price

You want to add a calculated column in the Sales table to fetch the ProductName from the Products table.

Step-by-Step Example

1. Ensure Relationship: Ensure there is a relationship between the Sales table and the Products table on the ProductID column.

2. Create Calculated Column:

   • Go to the Sales table in Power BI.

   • Create a new calculated column using the following DAX formula:

dax

Product Name = RELATED(Products[ProductName])

1. Result: This new column, Product Name, will now contain the name of the product for each sales transaction, fetched from the Products table.

Practical Use Cases

• Enhancing Data Models: Fetch additional details from related tables to enrich your data model.

• Reporting: Include related data in reports without having to manually join tables.

Tips

• Performance Considerations: Be mindful of performance when using RELATED extensively, especially with large datasets, as it can affect performance.

• Alternative: For complex data models or performance optimization, consider using other DAX functions or data model designs, such as LOOKUPVALUE or combining tables in the query editor.

Common Pitfalls

• Missing Relationships: If there’s no relationship between the tables, RELATED will not work.

• Cardinality Issues: Ensure the relationship is appropriately set (e.g., one-to-many) for RELATED to function correctly.

By understanding and effectively using the RELATED function, you can leverage Power BI’s capabilities to create more insightful and connected data visualizations.

Step-by-Step Example

1. Create Measure: Go to the Sales table and create a new measure with the following DAX formula:

   
   

Average Sales Amount =

IF(

    HASONEVALUE(Sales[ProductID]),

    AVERAGE(Sales[SalesAmount]),

    "Please select a single product"

)

1. Result:

   • If a single product is selected, the measure returns the average sales amount for that product.

   • If multiple products are selected, the measure returns the text “Please select a single product”.

Detailed Example

Let’s say we have a table Products with columns ProductID and ProductName. You want to display the total sales amount only if a single product is selected. Otherwise, you want to display a warning message.

1. Create Measure:

Total Sales Amount =

IF(

    HASONEVALUE(Products[ProductName]),

    SUM(Sales[SalesAmount]),

    "Please select a single product"

)

1. Use in Visual: Add this measure to a card visual in Power BI.

   • When a single product is selected, the card will display the total sales amount for that product.

   • When multiple products are selected or no product is selected, the card will display the warning message.

Practical Tips

• Combine with Other Functions: HASONEVALUE can be combined with other DAX functions like IF, VALUES, and SELECTEDVALUE for more complex logic.

• Debugging: Use HASONEVALUE to debug and ensure the correct context is being applied in complex calculations.

Additional Example

Suppose you want to create a dynamic measure that shows the sales amount for a selected category. If multiple categories are selected, it should show a different message.

Category Sales Amount =

IF(

    HASONEVALUE(Categories[CategoryName]),

    SUM(Sales[SalesAmount]),

    "Multiple categories selected"

)

Examples : 

Approximate Customer Count = APPROXIMATEDISTINCTCOUNT('Sales'[Customer ID])

Approximate Product Count = APPROXIMATEDISTINCTCOUNT('Sales'[Product ID])


Approximate Customer Count (East) = CALCULATE( APPROXIMATEDISTINCTCOUNT('Sales'[Customer ID]), 'Sales'[Region] = "East" )

Average Sales = AVERAGE('Sales'[Sales])

Now, assume the Discount column has the following data:

| Discount |
|———-|
| 10 |
| 20 |
| TRUE |
| FALSE |
| | (blank)
| 30 |

To calculate the average discount using AVERAGEA:

Average Discount =

AVERAGEA('Sales'[Discount])