DAX Function to Count Specific Rows

This document outlines the DAX function that counts the number of rows in a dataset where the QuestionID is 90 and the ResultText is either "no" or "NA".

Function Overview

• Purpose: To count rows based on specified criteria.
• Parameters: None required as it operates over a defined table.
• Return Type: Integer (count of rows matching the criteria).
• Exceptions: The function assumes the data types of columns are appropriate (e.g., integers for QuestionID and strings for ResultText).

DAX Code

Count_Question90_No_NA = 
COUNTROWS(
    FILTER(
        YourTableName, 
        YourTableName[QuestionID] = 90 && 
        (YourTableName[ResultText] = "no" || YourTableName[ResultText] = "NA")
    )
)

Code Explanation

1. COUNTROWS: This function counts the number of rows in a table.
2. FILTER: The FILTER function is used to apply the specified condition to the table.
3. Criteria:
   • YourTableName[QuestionID] = 90: Checks if QuestionID is equal to 90.
   • (YourTableName[ResultText] = "no" || YourTableName[ResultText] = "NA"): Checks if ResultText is either "no" or "NA".

Comments

• Be sure to replace YourTableName with the actual name of your data table in your Power BI model.
• This DAX expression efficiently filters the dataset and computes the desired count in one go.

Code Usage Example

In a scenario where you want to visualize the number of responses labeled "no" or "NA" for questions with ID 90 in your reporting tool (like Power BI), you can add this measure to your report.

Once you define the measure using the provided DAX code, you can:

• Add it to your visualizations.
• Use it in metrics or dashboards to analyze user feedback.

Summary

This DAX function provides a concise and effective way to count specific rows based on given conditions in your data model. Implementing this measure can facilitate deeper insights into your data, particularly in situations where you need to evaluate categorical responses.

For more advanced DAX techniques and insights, consider exploring the courses available on the Enterprise DNA Platform.