Creating new column with conditions

[Anonymous]

Folks,

I'm new to Power BI and DAX is still challenging.

I have multiple products eg A ... D

These are produced using diffetent processes eg PR1.... PR4.

On some days product process is changed from eg PR1 to PR2

Like table below

Products and Process changes log

Product ID	Process change date	Old Process	New Process	Comment
A	01.02.2024	PR1	PR2	Product A process change from PR1 to PR2 on 01Feb
B		PR1		Product B no change  still produced on process PR1
C	01.03.2024	PR3	PR4	Product C process change from PR3 to PR3 on 01Mar
D		PR3		Product D no change  still produced on process PR3

In Daily Production table I would like to show product and process being made on given day like below

Production date	Product ID	Product & Process
01.01.2024	B	B PR1
01.01.2024	A	A PR1
02.01.2024	D	D PR3
02.01.2024	A	A PR1
02.01.2024	B	B PR1
02.01.2024	C	C PR3
…
31.01.2024	A	A PR1
01.02.2024	A	A PR2
…
29.02.2024	C	C PR3
01.03.2024	C	C PR4
01.03.2024	A	A PR2

How to create last column "Product & Process"?

thanks 

[bhanu_gautam · Accepted solution]

@Anonymous , 

Create a Relationship: Ensure that there is a relationship between your "Products and Process changes log" table and your "Daily Production" table based on the Product ID.

Add a Calculated Column: In your "Daily Production" table, add a calculated column to determine the process for each product on a given date.

Here is the DAX formula to create the "Product & Process" column:

Product & Process =
VAR CurrentDate = 'Daily Production'[Production date]
VAR CurrentProduct = 'Daily Production'[Product ID]
VAR ProcessChange =
CALCULATE(
MAX('Products and Process changes log'[New Process]),
FILTER(
'Products and Process changes log',
'Products and Process changes log'[Product ID] = CurrentProduct &&
'Products and Process changes log'[Process change date] <= CurrentDate
)
)
VAR DefaultProcess =
CALCULATE(
MAX('Products and Process changes log'[Old Process]),
FILTER(
'Products and Process changes log',
'Products and Process changes log'[Product ID] = CurrentProduct &&
ISBLANK('Products and Process changes log'[Process change date])
)
)
RETURN
IF(
ISBLANK(ProcessChange),
CurrentProduct & " " & DefaultProcess,
CurrentProduct & " " & ProcessChange
)

[Anonymous]

This was really good help and hints for DAX functions. Thanks

[Anonymous · Accepted solution]

Hi @Anonymous ,

Create a new calculated column in your Daily Production table:

Product & Process = 
VAR CurrentDate = 'Daily Production'[Production date]
VAR ProductID = 'Daily Production'[Product ID]
VAR ProcessChangeDate = 
    CALCULATE(
        MAX('Products and Process Changes Log'[Process change date]),
        FILTER(
            'Products and Process Changes Log',
            'Products and Process Changes Log'[Product ID] = ProductID &&
            'Products and Process Changes Log'[Process change date] <= CurrentDate
        )
    )
VAR CurrentProcess = 
    IF(
        ISBLANK(ProcessChangeDate),
        LOOKUPVALUE('Products and Process Changes Log'[Old Process], 'Products and Process Changes Log'[Product ID], ProductID),
        LOOKUPVALUE('Products and Process Changes Log'[New Process], 'Products and Process Changes Log'[Product ID], ProductID, 'Products and Process Changes Log'[Process change date], ProcessChangeDate)
    )
RETURN
    ProductID & " " & CurrentProcess

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[bhanu_gautam · Accepted solution]

@Anonymous , 

Create a Relationship: Ensure that there is a relationship between your "Products and Process changes log" table and your "Daily Production" table based on the Product ID.

Add a Calculated Column: In your "Daily Production" table, add a calculated column to determine the process for each product on a given date.

Here is the DAX formula to create the "Product & Process" column:

Product & Process =
VAR CurrentDate = 'Daily Production'[Production date]
VAR CurrentProduct = 'Daily Production'[Product ID]
VAR ProcessChange =
CALCULATE(
MAX('Products and Process changes log'[New Process]),
FILTER(
'Products and Process changes log',
'Products and Process changes log'[Product ID] = CurrentProduct &&
'Products and Process changes log'[Process change date] <= CurrentDate
)
)
VAR DefaultProcess =
CALCULATE(
MAX('Products and Process changes log'[Old Process]),
FILTER(
'Products and Process changes log',
'Products and Process changes log'[Product ID] = CurrentProduct &&
ISBLANK('Products and Process changes log'[Process change date])
)
)
RETURN
IF(
ISBLANK(ProcessChange),
CurrentProduct & " " & DefaultProcess,
CurrentProduct & " " & ProcessChange
)