DAX Measure to Count Approved CMP Values

This section details how to create a DAX measure to count the number of "Yes" values in a column called Approved CMP, while also providing the maximum count and returning that value.

Objective

1. Count the Rows with Yes: Calculate the total number of rows that have the value "Yes" in the Approved CMP column.
2. Return Maximum Count: Ensure the measure returns the maximum count of these values.

DAX Code

Below is the DAX measure that fulfills the requirements.

Approved_CMP_Count_Yes =
VAR CountYes =
    COUNTROWS(
        FILTER(
            YourTableName, 
            YourTableName[Approved CMP] = "Yes"
        )
    )
RETURN
    CountYes

Explanation of the Code

1. Variable Declaration:

   • CountYes: A variable that calculates the count of rows in the YourTableName table where the Approved CMP column equals "Yes".

2. FILTER Function:

   • This function filters the rows of YourTableName, keeping only those records where Approved CMP is "Yes".

3. COUNTROWS Function:

   • It counts the rows returned by the FILTER function.

4. RETURN Statement:

   • The measure returns the count of "Yes" values.

Input Validation

Since DAX operates on tables and columns, this measure will automatically handle scenarios where:

• The column Approved CMP does not exist (the measure will not work).
• The table YourTableName might contain invalid data types, which will be ignored during the row count.

Usage Example

To deploy this measure in your Power BI report, follow these steps:

1. Open Power BI Desktop: Load your data model that contains the Approved CMP column.
2. Create a New Measure: In the Report view, navigate to the Fields pane, right-click on your table (e.g., YourTableName), and select "New Measure".
3. Paste the DAX Code: Use the provided DAX code, replacing YourTableName with the actual name of your table.
4. Visualize the Data: You can now use this measure in visualizations to display the count of approved entries.

Conclusion

This DAX measure provides a straightforward way to count and evaluate the "Yes" responses in your dataset, helping you in data analysis and reporting tasks. If you're looking to deepen your understanding of DAX measures, consider exploring the Enterprise DNA Platform for more comprehensive learning.