DAX Formula to Count Cases in Fiscal Week

Formula

To count the number of cases in a fiscal week, you can use the following DAX formula:

CasesInFiscalWeek = 
CALCULATE(
    COUNTROWS(Cases),
    FILTER(
        Cases,
        Cases[FiscalWeek] = SELECTEDVALUE(FiscalWeeks[FiscalWeek])
    )
)

Explanation

Components of the Formula

1. CALCULATE: This function modifies the filter context in which the data is evaluated. It allows for dynamic calculations based on the current context.

2. COUNTROWS: This function counts the number of rows in the specified table (in this case, the Cases table). It returns a scalar value representing the total number of cases.

3. FILTER: This function returns a table that contains only the rows that meet a specified condition. Here, it filters the Cases table based on the selected fiscal week.

4. SELECTEDVALUE: This function returns the value when there is a single value in the specified column; otherwise, it returns a blank. It is used to get the currently selected fiscal week from the FiscalWeeks table.

Logic

• The formula counts all rows in the Cases table where the FiscalWeek matches the selected fiscal week from the FiscalWeeks table.
• The use of CALCULATE allows the context to be modified appropriately, focusing on the specific fiscal week and enabling dynamic reporting.

Example

Scenario

Assuming you have a data model with the following tables:

• Cases: Contains case records with a column named FiscalWeek.
• FiscalWeeks: Contains unique fiscal weeks for filtering.

Implementation

1. Create a Measure: Use the formula above to create a new measure in your data model. Name it CasesInFiscalWeek.

2. Visual Representation:

   • In a Power BI report, you can place a card visual to display the measure.
   • Use a slicer to select a specific fiscal week from the FiscalWeeks table.

3. Expected Outcome: As you change the selected fiscal week using the slicer, the card visual will dynamically update to display the count of cases associated with that fiscal week.

Conclusion

This DAX formula effectively counts cases based on the fiscal week context you select in your reports. For further mastery of DAX and advanced analytical techniques, consider exploring courses available on the Enterprise DNA Platform.