create new table based on 3 tables

[bzeeblitz]

Table Name:Demand ;Note:No duplicate Item Number
Item Number	onhand>9 months	onhand>6 months
P123-AB-GG-AA-US	Less Than 9 months onhand	Less Than 6 months onhand
P123-AB-T-Z-US	more Than 9 months onhand	moreThan 6 months onhand
P123-E-BB-Z-US	Less Than 9 months onhand	Less Than 6 months onhand
215B14567	more Than 9 months onhand	more Than 6 months onhand

onhand>9 months is a calcualted column where i used this formula 

Onhand>9months = if('Demand'[Months of Available Inventory]>9,"More than 9 months On-Hand","Less than 9 months On-Hand")

onhand>6 months is a calcualted column where i used this formula 

Onhand>6months = if('Demand'[Months of Available Inventory]>6,"More than 6 months On-Hand","Less than 6 months On-Hand")

Table Name :cycle;Note:Multiple times Item# found
Item#	Order:Card Type	PO#
1-UPRIGHT	Card	21390
1-UPRIGHT	card	21390
1-UPRIGHT	card	21390
567890	Non card	21391
567890	non card	21391
567891	card	21392

Table Name : POItem;Note:Multiple times Item# found
Item#	Createdwhen	Vendor name
36 upright	PO not created yesterday	Welldone
36 upright	PO not created yesterday	welldone
36 upright	pO  created yesterday	welldone
38 upright	PO not created yesterday	vendor1

createdwhen is a calculated column where i used this formula

CreatedWhen = if('POitem'[Date: Line Created].[Day] = day(TODAY()-1),"PO Created Yesterday","PO Not Created Yesterday")

Action steps:

I tried to create new table and used the below formula for retrieving values from above 3 tables and i can see below columns created but i need to add extra columns vendorname column from POitem Table and cardType column from cycle table and we need to also check for Item# respectively from these two table also there are possible of Item# occuring multiple times.

Kindly help

Below code runs for non blank values as well so we need to remove nonblankvalues for createdwhen column if its blank value incase

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], -- This should be a single column

'poitem'[CreatedWhen] -- We are evaluating CreatedWhen for non-blank valu

[DataNinja777 · Accepted solution]

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,