Table visuals report

Hi bzeeblitz ,

To refine your code to handle the scenario for three tables and meet your requirements, the logic must ensure that data from all three tables is integrated correctly while maintaining clarity and efficiency.

Below is the refined DAX formula to include logic for three tables, assuming the third table is called Orders with a relevant column Orders[OrderDate] that needs to be included in the calculation:

Table =
SELECTCOLUMNS(
    VALUES('Demand'[Item Number]), -- Ensure only unique Item Numbers
    "Greater than 9 Months On-Hand",
        CALCULATE(
            MAX('Demand'[Onhand>9months])
        ),
    "Greater than 6 Months On-Hand",
        CALCULATE(
            MAX('Demand'[Onhand>6months])
        ),
    "Item Number",
        'Demand'[Item Number],
    "CreatedWhen",
        FIRSTNONBLANK(
            'poitem'[CreatedWhen], -- Extract non-blank CreatedWhen
            'poitem'[CreatedWhen]
        ),
    "Order Date",
        FIRSTNONBLANK(
            'Orders'[OrderDate], -- Extract non-blank Order Date
            'Orders'[OrderDate]
        )
)

To enhance the integration of the third table, the Orders table has been included, specifically incorporating the Orders[OrderDate] column to ensure its relevant data is part of the calculation. Each column has been reviewed for clarity, ensuring meaningful names and corresponding values. The logic for handling non-blank values has been implemented using the FIRSTNONBLANK function to extract the first available non-blank value from the Orders table.

It is important to ensure that relationships between the Demand, poitem, and Orders tables are properly defined within the data model to facilitate accurate calculations. The columns [Onhand>9months] and [Onhand>6months] must already exist in the Demand table for this formula to function as intended. Additionally, verify that the CreatedWhen and OrderDate fields are correctly populated and mapped to their respective tables.

Best regards,