DAX Function for Target Count of BL Schedule

This guide outlines a DAX function designed to count the target of BL schedules based on specific conditions. The function uses the FILTER function to filter through the 00 Project Data table, checking dates, contract numbers, and schedule approval status.

Overview of the DAX Function

The goal of the DAX function is to quantify BL Schedule entries linked to contracts that have a delivery date plus thirty days that meets or exceeds today's date. Additionally, it considers only approved entries in the 02 BL Schedule.

Parameters

• Uses the 00 Project Data and 02 BL Schedule tables.
• Filters entries based on the current date and conditions.

Structure of the Function

Target Count of BL Schedule = 
VAR CurrentDate = TODAY()
RETURN
    COUNTROWS(
        FILTER(
            '00 Project Data',
            '00 Project Data'[تاريخ تسليم الموقع للمقاول - G] + 30 <= CurrentDate &&
            '00 Project Data'[رقم العقد التشغيلي] IN VALUES('02 BL Schedule'[رقم العقد التشغيلي]) &&
            CALCULATE(
                COUNTROWS('02 BL Schedule'),
                '02 BL Schedule'[رقم العقد التشغيلي] = '00 Project Data'[رقم العقد التشغيلي],
                '02 BL Schedule'[Approved BL schedule?] = "yes"
            ) > 0
        )
    )

Documentation

/**
 * Target Count of BL Schedule
 * 
 * This function calculates the number of BL Schedule entries that meet specific criteria.
 * 
 * Returns:
 * - Integer representing the count of applicable BL Schedule entries.
 *
 * Exceptions:
 * - Ensure that the columns used exist in the respective tables.
 */

Breakdown of the Function Logic

1. Current Date Calculation:

   • The function initializes the variable CurrentDate with the current date using TODAY().

2. Count Rows:

   • The COUNTROWS function wraps around FILTER, which filters records based on a series of conditions.

3. Date Condition:

   • Filters rows where the delivery date (plus 30 days) is less than or equal to the current date.

4. Contract Condition:

   • Checks if the contract number from 00 Project Data exists in the 02 BL Schedule.

5. Approved Schedule Check:

   • Uses CALCULATE to ensure that there is at least one approved entry in the 02 BL Schedule for the given contract number.

Code Usage Example

To implement this function in your Power BI model, you can create a new measure within the Modeling tab of Power BI:

1. Go to the Modeling tab.
2. Click on New Measure.
3. Paste the above DAX code.
4. Use this measure in your reports to display the count of target BL schedules based on your defined conditions.

Conclusion

The DAX function effectively counts the target BL schedules based on delivery dates, existence of contracts, and approval status. Ensure that your data model and relationships are correctly set up for optimal performance.

For further insights into DAX and advanced analytics, consider exploring courses on the Enterprise DNA platform.