DAX Function to Display Negative Values as Dash

Description

This DAX function will modify the behavior of a measure to display a dash (-) for any negative values instead of hiding the row. This approach ensures that all results are visible while clearly indicating negative numbers.

Function Definition

DisplayValue =
VAR CurrentValue = [YourMeasure]  // Replace 'YourMeasure' with your actual measure name
RETURN
    IF(CurrentValue < 0, "-", CurrentValue)

Explanation

• Variable Declaration (VAR): We create a variable CurrentValue to store the value of the measure we're working with. This makes our code cleaner and enhances readability.
• RETURN Statement: We use the IF function to check if CurrentValue is less than zero.
  • If true, it returns "-" indicating a negative value.
  • If false, it returns the actual CurrentValue.

Function Components

• Input: The measure to evaluate (YourMeasure).
• Output: A string representation of the measure’s value, displaying "-" for negatives and the measure’s value for non-negatives.
• No Exceptions: There are no exceptions to handle as this function operates purely on numerical comparisons.

Input Validation

The function assumes that YourMeasure is a numerical measure. Ensure that YourMeasure is appropriately defined in your data model.

Usage Example

Assuming you have a measure called Total Sales, you can invoke this function as follows:

Sales Display = 
VAR CurrentSales = [Total Sales]
RETURN
    IF(CurrentSales < 0, "-", CurrentSales)

Scenario

In a financial report displaying total sales figures:

• If the Total Sales measure results in -500, the display will show a dash -.
• If the result is 200, it will display 200.

Conclusion

This DAX function provides a straightforward way to manage negative values in reports, ensuring that all data rows remain visible while clearly indicating negatives with a simple -. Implementing this function enhances data readability and user experience in reports, especially suitable for financial dashboards.

For further enhancement of your DAX skills, consider exploring courses available on the Enterprise DNA Platform.