create new table based on 3 tables

Hi bzeeblitz ,

To achieve your desired result, we need to update your DAX formula to include the additional columns Vendor Name from the POItem table and Card Type from the Cycle table, while also ensuring that CreatedWhen handles non-blank values appropriately. Here’s the updated DAX for creating the new table:

Table =
SELECTCOLUMNS(
    VALUES('Demand'[Item Number]), -- Ensure only unique Item Numbers
    "Item Number", 'Demand'[Item Number],
    "Greater than 9 Months On-Hand", CALCULATE(MAX('Demand'[Onhand>9months])),
    "Greater than 6 Months On-Hand", CALCULATE(MAX('Demand'[Onhand>6months])),
    "CreatedWhen", 
    FIRSTNONBLANK(
        FILTER(
            'POItem',
            NOT(ISBLANK('POItem'[CreatedWhen]))
        )['CreatedWhen'],
        'POItem'[CreatedWhen]
    ),
    "Vendor Name", 
    FIRSTNONBLANK(
        FILTER(
            'POItem',
            NOT(ISBLANK('POItem'[CreatedWhen]))
        )['Vendor name'],
        'POItem'[Vendor name]
    ),
    "Card Type", 
    FIRSTNONBLANK(
        FILTER(
            'Cycle',
            'Cycle'[Item#] = 'Demand'[Item Number]
        )['Order:Card Type'],
        'Cycle'[Order:Card Type]
    )
)

The SELECTCOLUMNS function is used to create the new calculated table and explicitly define the columns required for the table. The column "Item Number" retrieves unique item numbers from the Demand table. The columns "Greater than 9 Months On-Hand" and "Greater than 6 Months On-Hand" use the CALCULATE function with MAX to extract the corresponding values. The "CreatedWhen" column retrieves the first non-blank value of CreatedWhen from the POItem table, ensuring only relevant entries are included. Similarly, the "Vendor Name" column retrieves the first non-blank Vendor Name from the POItem table. The "Card Type" column filters the Cycle table based on the matching Item# with the Demand table’s Item Number and retrieves the first non-blank value of Order:Card Type.

To handle non-blank values, the FILTER function is applied to ensure only rows with valid entries for CreatedWhen in the POItem table are considered. This ensures the resulting data is clean and adheres to the conditions specified.

When dealing with multiple occurrences of Item# in the Cycle or POItem tables, the FIRSTNONBLANK function is used to extract the first valid entry for each case. This approach is suitable for creating summary tables, but adjustments may be needed if more detailed or aggregated data is required.

Once this table is created, it can be used directly in Power BI visualizations to display the required data. If further modifications or enhancements are needed, those can be made based on specific requirements.

Best regards,