DAX Measure to Count Records in Two Scenarios

This section provides a DAX solution to create a measure that counts the records in a specified table, both when the table is not filtered and when it is being filtered by a slicer.

Overview

• Count Records: Measure that counts the total records in the table.
• Count Filtered Records: Measure that counts records based on any active filters from slicers.

Code Implementation

1. Measure to Count All Records

This measure counts all records in the specified table regardless of any filters.

TotalRecords = COUNTROWS(TableName)

2. Measure to Count Filtered Records

This measure counts records in the specified table while considering filters applied through slicers.

FilteredRecords = 
VAR CurrentContext = CALCULATE(COUNTROWS(TableName), ALLSELECTED(TableName))
RETURN
    CurrentContext

Explanation of the Code

TotalRecords Measure

• COUNTROWS(TableName): This function simply counts all rows in the specified TableName without considering any filters.

FilteredRecords Measure

• VAR CurrentContext: A variable is defined to store the result of counting rows while considering the current filters from slicers.
• CALCULATE(...): This function changes the context in which the data is evaluated.
• ALLSELECTED(TableName): This removes context filters from the table while retaining any slicer selections. It effectively allows counting rows while respecting filters applied to the table through slicers.
• RETURN: Returns the count of rows based on the filtered context.

Input Validation

While DAX measures generally do not require explicit input validation because they operate directly on the data model, ensure that:

• TableName exists in your data model.
• Relevant slicers are correctly set up in your report.

Example Usage

Assuming you have a data table named SalesData and you want to count total sales records and also count sales filtered by a "Product Category" slicer, you would implement the measures as:

TotalSalesRecords = COUNTROWS(SalesData)

FilteredSalesRecords = 
VAR CurrentContext = CALCULATE(COUNTROWS(SalesData), ALLSELECTED(SalesData))
RETURN
    CurrentContext

Reporting Scenario

• TotalSalesRecords will return the total number of sales recorded in your dataset.
• FilteredSalesRecords will adjust based on the product category selected in a slicer, allowing dynamic insights based on user interaction.

Conclusion

This solution provides a clear way to measure counts in DAX either filtering contextually or aggregating total rows. This dual-measure setup enhances your reporting capability in Power BI, allowing for dynamic interactions and accurate insights.

For further in-depth learning, consider exploring courses on the Enterprise DNA Platform.