Code Generator | DAX

No Show Rate DAX Function with Negative Handling

This DAX measure calculates the no-show rate, returning a hyphen for negative no-show counts instead of a blank. It sums registered attendees, actual attendees, and computes the rate, enhancing visibility in Power BI reports.

Prompt

fix this it is hiding rows if its negative, I just want it to display a - if negative NoShowRate1 = 
VAR RegisteredCount = SUM('Attendance'[Registered])   // Calculate total registered attendees
VAR AttendanceCount = SUM('Attendance'[Attendance])   // Calculate total number of attendees
VAR NoShowCount = RegisteredCount - AttendanceCount    // Determine the number of no-shows
RETURN 
    IF(NoShowCount <= 0,                                // Check if NoShowCount is negative
       BLANK(),                                       // Return blank if it's negative
       NoShowCount / RegisteredCount                   // Calculate the No Show Rate otherwise
    )

Answer

DAX Function to Calculate No Show Rate with Negative Handling

Problem Description

The current DAX measure NoShowRate1 calculates the no-show rate but hides rows when the no-show count is negative. We want to adjust this measure to display a hyphen (-) instead of returning a blank when the no-show count is negative.

Revised DAX Measure

Here’s the revised DAX code that fulfills the requirement:

NoShowRate1 = 
VAR RegisteredCount = SUM('Attendance'[Registered])   // Calculate total registered attendees
VAR AttendanceCount = SUM('Attendance'[Attendance])   // Calculate total number of attendees
VAR NoShowCount = RegisteredCount - AttendanceCount    // Determine the number of no-shows
RETURN 
    IF(NoShowCount < 0,                                // Check if NoShowCount is negative
       "-",                                           // Return '-' if it's negative
       NoShowCount / RegisteredCount                   // Calculate the No Show Rate otherwise
    )

Explanation of Code

Variable Definitions:
- RegisteredCount: Sums the total registered attendees from the 'Attendance' table.
- AttendanceCount: Sums the total number of attendees from the same table.
- NoShowCount: Computes the difference between registered attendees and actual attendees.
Return Value:
- If NoShowCount is less than zero, the function returns a string ("-").
- If NoShowCount is zero or more, it calculates the no-show rate as the NoShowCount divided by RegisteredCount.

Usage Example

To use this measure in a Power BI report:

Add the NoShowRate1 measure to a card visual to display the no-show rate.
Use it in a table or matrix visual along with other data fields from the 'Attendance' table to review attendance trends.

Conclusion

This measure effectively handles the requirement to display a hyphen for negative no-show counts instead of hiding the rows. Remember to check for correct context when using this measure in visuals to ensure accurate calculations based on filter selections.

If you are interested in learning more about DAX and enhancing your skills, consider exploring courses on the Enterprise DNA Platform that cover advanced DAX techniques and best practices.