Implementation of Rule #16 in Power BI DAX

This document outlines the function for Rule #16 using DAX in Power BI. It calculates a distinct count based on specific criteria.

Function Overview

The function defined computes the distinct count of a combination of ValuationDate and CntTagUniqueName from the Rule16_EYR_CheckYieldRateValues table when the YieldRateRule is equal to 1. If the result is blank, it returns "0".

DAX Code

Rule #16 = 
VAR _DisCountR16 =
    VAR ValuationDate = Rule16_EYR_CheckYieldRateValues[ValuationDate]
    VAR CntTag = Rule16_EYR_CheckYieldRateValues[CntTagUniqueName]

    CALCULATE(
        DISTINCTCOUNT(
            [ValuationDate & " - " & CntTag]
        ),
        'Rule16_EYR_CheckYieldRateValues'[YieldRateRule] IN { 1 }
    )
RETURN
    IF(ISBLANK(_DisCountR16), "0", _DisCountR16)

Code Explanation

1. Variable Declaration:

   • ValuationDate and CntTag store the current row's values of ValuationDate and unique tag names respectively.

2. Calculate Distinct Count:

   • CALCULATE adjusts the context in which the data is evaluated, allowing us to count distinct combinations of ValuationDate and CntTagUniqueName.
   • DISTINCTCOUNT ensures only unique combinations are counted.

3. Yield Rate Condition:

   • The filter condition 'Rule16_EYR_CheckYieldRateValues'[YieldRateRule] IN { 1 } ensures only records where YieldRateRule equals 1 are included in the count.

4. Return Statement:

   • IF(ISBLANK(_DisCountR16), "0", _DisCountR16) returns "0" if the count is blank, ensuring that the output is always a string.

Example Usage

Here’s how to use this DAX formula in a Power BI report:

• To create a new measure, navigate to the "Modeling" tab, select "New Measure", and paste the DAX code above.
• You can then drag and drop this measure into your visualizations to see the distinct count based on your input conditions.

Conclusion

This DAX code is efficient, targeting a specific context to deliver the desired output while incorporating input validation via the IF statement to handle blank results. For further learning on DAX and Power BI, consider exploring courses on Enterprise DNA Platform to enhance your skills and methods in data analysis.