DAX Measure for Counting Blank Disconnect Dates

Problem Analysis

The provided DAX code attempts to determine if there are specific rows in the NEW.DIM_Bills table based on the DisconnectDate. The main issues in the code include:

1. Incorrect Closing Parentheses and Syntax Errors - The FILTER function is not formatted correctly. The condition should be a logical expression.
2. Incorrect Literal for 'Blank' - The proper method to check for a blank value in DAX is ISBLANK() or comparing directly with BLANK().
3. Use of Countrows - This needs to be structured properly to return the count of rows based on the filtering conditions.

Previous Attempts

No previous attempts were detailed in the user request. Therefore, a fresh approach will be developed.

Solution Proposal

To determine if there are any disconnect dates marked as blank, we will modify the DAX query. The primary approach will involve:

1. Using SUMMARIZE to create a summarized table.
2. Filtering this summarized table based on the criteria that DisconnectDate is blank.
3. Returning the count of rows that meet the filter criteria.

Revised DAX Code

Disco Yes/No = 
VAR DiscoRows =
    SUMMARIZE(
        'NEW.DIM_Bills',
        'NEW.DIM_Bills'[UserName],
        'NEW.DIM_Bills'[DisconnectDate],
        'NEW.DIM_Bills'[BillId]
    )

VAR FilteredDiscoRows =
    FILTER(
        DiscoRows,
        ISBLANK('NEW.DIM_Bills'[DisconnectDate])
    )

RETURN
    COUNTROWS(FilteredDiscoRows)

Code Explanation

• SUMMARIZE creates a temporary table (DiscoRows) with unique records based on UserName, DisconnectDate, and BillId.
• FILTER applies a condition to only include rows where DisconnectDate is blank.
• COUNTROWS counts the filtered rows to provide the final output.

Code Usage Example

Assuming you are using this code within a DAX measure in Power BI or similar tools, you can implement this as follows:

1. Open the Power BI report view.
2. Select the table where you want this measure to appear.
3. In the formula bar, enter the revised DAX code above.
4. Use the measure Disco Yes/No within any visual, such as a card or table, to display the count of disconnects with blank dates.

Conclusion

The provided DAX measure will efficiently filter and count the number of entries with a blank DisconnectDate. Ensure that the field references match your data model for accurate results. If there are further challenges or adjustments needed, deeper investigation into the data model may be required.