Formula Generator | DAX

Total Declarations DAX Formula

This guide details an enhanced DAX formula that calculates total declarations while ignoring filters on the status column. It maintains existing deadline filters, providing comprehensive insights into declaration counts in data analysis.

Prompt

Will have total without looking ath the filter of declarations [status] but as an additional step of the below formula: 

No of Declarations = 
VAR SelectedDeadline = SELECTEDVALUE('SlicerTable'[Deadline]; "Payment")
RETURN
SWITCH(
    SelectedDeadline;
    "Payment";
    CALCULATE(
        DISTINCTCOUNTNOBLANK('Declarations'[Declaration ID]);
        'Declarations'[External Declaration Timeliness] <> "No External Deadline";
        USERELATIONSHIP('Declarations'[Filled Dates External]; Calendar[Date])
    );
    "Filing";
    CALCULATE(
        DISTINCTCOUNTNOBLANK('Declarations'[Declaration ID]);
        'Declarations'[Internal Declaration Timeliness] <> "No Internal Deadline";
        USERELATIONSHIP('Declarations'[Filled Dates Internal]; Calendar[Date])
    );
    // Default case
   CALCULATE(
        DISTINCTCOUNTNOBLANK('Declarations'[Declaration ID]);
        'Declarations'[External Declaration Timeliness] <> "No External Deadline";
        USERELATIONSHIP('Declarations'[Filled Dates External]; Calendar[Date])))

Answer

DAX Formula for Total Declarations Ignoring Filters on Status

Objective

To create a DAX formula that calculates the total number of declarations while ignoring any filters on the [status] column, in addition to the existing formula for counting declarations based on deadlines.

Existing Formula

The provided formula, No of Declarations, utilizes the SWITCH function to choose counts based on the selected deadline. Below is a recap of the original formula:

No of Declarations = 
VAR SelectedDeadline = SELECTEDVALUE('SlicerTable'[Deadline]; "Payment")
RETURN
SWITCH(
    SelectedDeadline;
    "Payment";
    CALCULATE(
        DISTINCTCOUNTNOBLANK('Declarations'[Declaration ID]);
        'Declarations'[External Declaration Timeliness] <> "No External Deadline";
        USERELATIONSHIP('Declarations'[Filled Dates External]; Calendar[Date])
    );
    "Filing";
    CALCULATE(
        DISTINCTCOUNTNOBLANK('Declarations'[Declaration ID]);
        'Declarations'[Internal Declaration Timeliness] <> "No Internal Deadline";
        USERELATIONSHIP('Declarations'[Filled Dates Internal]; Calendar[Date])
    );
    // Default case
    CALCULATE(
        DISTINCTCOUNTNOBLANK('Declarations'[Declaration ID]);
        'Declarations'[External Declaration Timeliness] <> "No External Deadline";
        USERELATIONSHIP('Declarations'[Filled Dates External]; Calendar[Date])
    )
)

Enhanced Formula

To include a total count of declarations ignoring the [status] filter, an additional CALCULATE function will be incorporated. Here is the updated DAX formula:

Total Declarations Ignoring Status = 
VAR SelectedDeadline = SELECTEDVALUE('SlicerTable'[Deadline]; "Payment")
VAR TotalDeclarations = 
    CALCULATE(
        DISTINCTCOUNTNOBLANK('Declarations'[Declaration ID]);
        ALL('Declarations'[status])
    )
RETURN
SWITCH(
    SelectedDeadline;
    "Payment";
    CALCULATE(
        DISTINCTCOUNTNOBLANK('Declarations'[Declaration ID]);
        'Declarations'[External Declaration Timeliness] <> "No External Deadline";
        USERELATIONSHIP('Declarations'[Filled Dates External]; Calendar[Date])
    );
    "Filing";
    CALCULATE(
        DISTINCTCOUNTNOBLANK('Declarations'[Declaration ID]);
        'Declarations'[Internal Declaration Timeliness] <> "No Internal Deadline";
        USERELATIONSHIP('Declarations'[Filled Dates Internal]; Calendar[Date])
    );
    // Default case
    CALCULATE(
        DISTINCTCOUNTNOBLANK('Declarations'[Declaration ID]);
        'Declarations'[External Declaration Timeliness] <> "No External Deadline";
        USERELATIONSHIP('Declarations'[Filled Dates External]; Calendar[Date])
    )
)
+ TotalDeclarations

Explanation of the Enhanced Formula

Variable Initialization:
- SelectedDeadline: Captures the deadline selected in the slicer. Defaults to "Payment" if no selection is made.
- TotalDeclarations: Computes the total distinct count of declaration IDs while ignoring any active filter on the [status] field by utilizing the ALL function.
SWITCH Logic: The existing SWITCH logic remains intact, adjusting only to append TotalDeclarations to the result, hence maintaining the focus on deadlines while also presenting the overall count.

Practical Example

Assuming your Declarations table has the following data:

Declaration ID	External Declaration Timeliness	Internal Declaration Timeliness	Filled Dates External	Filled Dates Internal	Status
1	On Time	No Internal Deadline	2023-01-01	2023-01-05	Active
2	No External Deadline	On Time	2023-01-02	2023-01-06	Inactive
3	On Time	On Time	2023-01-03	2023-01-07	Active
4	On Time	On Time	2023-01-04	2023-01-08	Completed

Using the enhanced formula:

If "Payment" is selected, it counts only those valid external deadlines while ignoring the status.
If "Filing" is selected, a similar approach is applied for internal declaration timeliness.
The total declarations that bypass the status filter give an additional insight into the overall declarations.

Conclusion

This DAX formula with its additional step successfully provides a total count of declarations while accommodating the requirements related to filtering based on deadlines, enhancing the analytical capabilities for users. For an in-depth understanding of DAX and advanced techniques, consider exploring the Enterprise DNA Platform.