DAX Function to Count Rows Based on Conditions

Overview

This DAX function is designed to count the number of rows in a dataset where the QuestionID is 90 and the ResultText is "No". It utilizes the COUNTROWS and FILTER functions to apply the necessary conditions.

Function Definition

CountRows_QuestionID90_No = 
VAR FilteredData = 
    FILTER(
        YourTableName, 
        YourTableName[QuestionID] = 90 && YourTableName[ResultText] = "No"
    )
RETURN
    COUNTROWS(FilteredData)

Explanation

• VAR FilteredData: This variable stores the rows filtered by the specified conditions: QuestionID equals 90 and ResultText equals "No".
• FILTER: This function iterates through YourTableName, returning only the rows that meet the specified criteria.
• COUNTROWS: Counts the number of rows in the FilteredData variable.

Input Validation

While DAX inherently doesn’t provide explicit input validation like traditional programming languages, ensure that:

• The data types for QuestionID and ResultText are compatible (e.g., QuestionID is numeric and ResultText is text).

Commentary

• The code snippet assumes the existence of a table named YourTableName. This should be replaced with your actual table name.
• The use of variables (VAR) makes the code more readable and potentially improves performance.

Code Usage Example

To use this function, create a measure in your Power BI report:

1. Go to Modeling.
2. Click on New measure.
3. Paste the DAX function above, ensuring you replace YourTableName with the name of your dataset.

Sample Output

• If your data contains the following records:

  QuestionID	ResultText
  90	Yes
  90	No
  90	No
  91	No

The measure CountRows_QuestionID90_No would return 2, as there are two rows where QuestionID is 90 and ResultText is "No".

Conclusion

This DAX function efficiently counts rows based on specific criteria and is well-suited for use within Power BI. For deeper insights into DAX and advanced coding techniques, consider exploring courses offered by the Enterprise DNA Platform.