Day 13: Logical, Information, and Filter Functions in Power BI DAX

On Day 13, we explore logical functions, information functions, and filter functions in DAX. These functions allow you to perform conditional calculations, validate data, and apply advanced filtering for precise analysis.

1. Logical Functions

Logical functions help you implement conditional logic in DAX formulas.

1. SWITCH() – Evaluates an expression against multiple values and returns a result based on the first match.

2. IF() – Performs an IF-ELSE condition.

3. AND() – Checks if multiple conditions are true simultaneously.

4. OR() – Checks if at least one condition is true.

5. TRUE/FALSE() – Returns a Boolean value.

Example:

This categorizes sales as “High” or “Low” based on a threshold.

2. Information Functions

Information functions validate or inspect data types and content:

1. ISEVEN() – Checks if a number is even.

2. ISODD() – Checks if a number is odd.

3. ISBLANK() – Checks if a value is blank.

4. ISNUMBER() – Checks if a value is numeric.

5. ISTEXT() – Checks if a value is text.

6. ISNONBLANK() – Checks if a value is not blank.

7. ISNONTEXT() – Checks if a value is not text.

8. ISLOGICAL() – Checks if a value is Boolean.

9. ISFILTERED() – Checks if a column or table is filtered.

10. ISNULL() – Checks if a value is null.

These functions are useful for data validation, error handling, and conditional calculations.

3. Filter Functions

Filter functions allow you to control context and filter tables in DAX calculations. Key functions include:

1. CALCULATE() – Evaluates an expression in a modified filter context and returns a scalar value.

   • Single filter:

   CALCULATE(SUM(Orders[Sales]), Orders[Region] = "Central")


   • Multiple filters:

   CALCULATE(SUM(Orders[Sales]), Orders[Region] = "Central", Orders[Ship Mode] = "First Class")


   • Using AND:

   CALCULATE(SUM(Orders[Sales]), AND(Orders[Region] = "Central", Orders[Segment] = "Consumer"))


   • Using OR:

   CALCULATE(SUM(Orders[Sales]), OR(Orders[Region] = "Central", Orders[Segment] = "Consumer"))


2. Commenting in CALCULATE:

   FurnitureSales = CALCULATE(
SUM(Orders[Sales]),
//Orders[Region] = "Central",
Orders[Product Category] = "Furniture"
)


3. CALCULATETABLE() – Creates a new table based on an existing table with applied filters.

   • Example:

   CentralTable = CALCULATETABLE(Orders, Orders[Region] = "Central")


   • With multiple filters:

   CentralTable = CALCULATETABLE(
Orders,
//Orders[Region] = "Central",
Orders[Product Category] = "Furniture"
)


Summary

• Logical functions allow conditional evaluation.

• Information functions validate and inspect data.

• Filter functions like CALCULATE and CALCULATETABLE control the context for precise calculations and table creation.

Mastering these DAX functions is essential for dynamic, context-aware reporting in Power BI.