Daily DAX : Day 348 SIGN

Power BI DAX SIGN Function

Description

The SIGN function in DAX (Data Analysis Expressions) returns an integer indicating the sign of a number:

• 1 if the number is positive.
• 0 if the number is zero.
• -1 if the number is negative.

Syntax

SIGN(<number>)

Parameter:

• number: A numeric expression or column containing a number.

Return Value

An integer: 1, 0, or -1.

Use Case

The SIGN function is useful for categorizing or analyzing data based on whether values are positive, negative, or zero. Common scenarios include:

• Financial Analysis: Identify profit (positive) or loss (negative) in financial metrics.
• Trend Analysis: Determine the direction of change in values (e.g., sales growth or decline).
• Conditional Formatting: Apply visual indicators based on the sign of a value.

Example

Suppose you have a table Sales with a column Profit. You want to categorize each row based on the sign of the profit.

ProfitSign = SIGN(Sales[Profit])

This creates a calculated column where:

• 1 indicates a profit.
• 0 indicates break-even.
• -1 indicates a loss.

Practical Application

You can use the ProfitSign column in visuals or measures, such as:

ProfitStatus = 
SWITCH(
    SIGN(Sales[Profit]),
    1, "Profit",
    0, "Break-even",
    -1, "Loss"
)

This measure labels each row as "Profit", "Break-even", or "Loss" for reporting.

Notes

• The input must be a numeric value or expression.
• If the input is non-numeric, DAX returns an error.
• Useful in combination with other DAX functions like SWITCH or IF for conditional logic.