Power BI & Tableau Resources
tg55 тыс. подписчиков
Открыть пост ↗
📊 Power BI — Essential DAX Functions with Examples
DAX is used for calculations in the Power BI data model. These are the functions you should learn first.
1. SUM
Adds values from a column.
Use it when you need the total sales amount.
2. AVERAGE
Returns the average value.
3. COUNT
Counts numeric values.
4. COUNTROWS
Counts the number of rows in a table.
This is often more useful than COUNT when you want to count records.
5. DISTINCTCOUNT
Counts unique values.
If the same customer made 10 orders, they are counted only once.
6. MIN and MAX
Find the minimum and maximum values.
🔹 Logical Functions
7. IF
Returns one result when a condition is TRUE and another when it is FALSE.
8. SWITCH
Useful when you have multiple conditions.
For multiple conditions, SWITCH(TRUE()) is often cleaner than deeply nested IF statements.
9. AND / OR
&& means AND.
|| means OR.
🔹 Filter Functions
10. CALCULATE
One of the most important DAX functions.
It evaluates an expression under a modified filter context.
This calculates Total Sales only for Online transactions.
Understanding CALCULATE is essential for advanced DAX.
11. FILTER
Creates a filtered table.
FILTER is especially useful when the filtering condition is more complex.
12. ALL
Removes filters from a table or column.
This can be used to calculate a value against the overall total regardless of the Region filter.
13. REMOVEFILTERS
Explicitly removes filters.
This is often clearer than using ALL when your intention is simply to remove a filter.
14. ALLEXCEPT
Removes filters except those specified.
Useful when you want to preserve a particular grouping while removing other filters.
🔹 Relationship Functions
15. RELATED
Retrieves a value from a related table.
Suppose:
Sales[ProductID]
is related to:
Products[ProductID]
You can write:
This is mainly used in calculated columns.
16. RELATEDTABLE
Returns the related rows from another table.
It is particularly useful when working from the one-side table toward the many-side table.
Example:
DAX is used for calculations in the Power BI data model. These are the functions you should learn first.
1. SUM
Adds values from a column.
Total Sales = SUM(Sales[SalesAmount])Use it when you need the total sales amount.
2. AVERAGE
Returns the average value.
Average Sales = AVERAGE(Sales[SalesAmount])3. COUNT
Counts numeric values.
Order Count = COUNT(Sales[OrderID])4. COUNTROWS
Counts the number of rows in a table.
Total Orders = COUNTROWS(Sales)This is often more useful than COUNT when you want to count records.
5. DISTINCTCOUNT
Counts unique values.
Unique Customers = DISTINCTCOUNT(Sales[CustomerID])If the same customer made 10 orders, they are counted only once.
6. MIN and MAX
Find the minimum and maximum values.
Minimum Sale = MIN(Sales[SalesAmount])
Maximum Sale = MAX(Sales[SalesAmount])🔹 Logical Functions
7. IF
Returns one result when a condition is TRUE and another when it is FALSE.
Sales Category =
IF(
[Total Sales] >= 100000,
"High",
"Low"
)8. SWITCH
Useful when you have multiple conditions.
Sales Rating =
SWITCH(
TRUE(),
[Total Sales] >= 500000, "Excellent",
[Total Sales] >= 250000, "Good",
[Total Sales] >= 100000, "Average",
"Low"
)For multiple conditions, SWITCH(TRUE()) is often cleaner than deeply nested IF statements.
9. AND / OR
Eligible =
IF(
Sales[Amount] > 10000 &&
Sales[Status] = "Completed",
"Yes",
"No"
)&& means AND.
|| means OR.
🔹 Filter Functions
10. CALCULATE
One of the most important DAX functions.
It evaluates an expression under a modified filter context.
Online Sales =
CALCULATE(
[Total Sales],
Sales[Channel] = "Online"
)This calculates Total Sales only for Online transactions.
Understanding CALCULATE is essential for advanced DAX.
11. FILTER
Creates a filtered table.
High Value Sales =
CALCULATE(
[Total Sales],
FILTER(
Sales,
Sales[SalesAmount] > 10000
)
)FILTER is especially useful when the filtering condition is more complex.
12. ALL
Removes filters from a table or column.
Total Sales All Regions =
CALCULATE(
[Total Sales],
ALL(Sales[Region])
)This can be used to calculate a value against the overall total regardless of the Region filter.
13. REMOVEFILTERS
Explicitly removes filters.
Total Sales =
CALCULATE(
[Total Sales],
REMOVEFILTERS(Sales[Region])
)This is often clearer than using ALL when your intention is simply to remove a filter.
14. ALLEXCEPT
Removes filters except those specified.
Sales By Region =
CALCULATE(
[Total Sales],
ALLEXCEPT(Sales, Sales[Region])
)Useful when you want to preserve a particular grouping while removing other filters.
🔹 Relationship Functions
15. RELATED
Retrieves a value from a related table.
Suppose:
Sales[ProductID]
is related to:
Products[ProductID]
You can write:
Product Category =
RELATED(Products[Category])This is mainly used in calculated columns.
16. RELATEDTABLE
Returns the related rows from another table.
It is particularly useful when working from the one-side table toward the many-side table.
Example:
Новый список