Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
KamilNewOnPBI
New Member

Creating new column with conditions

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 IDProcess change dateOld ProcessNew ProcessComment
A01.02.2024PR1PR2Product A process change from PR1 to PR2 on 01Feb
B PR1 Product B no change  still produced on process PR1
C01.03.2024PR3PR4Product 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 dateProduct IDProduct & Process
01.01.2024BB PR1
01.01.2024AA PR1
02.01.2024DD PR3
02.01.2024AA PR1
02.01.2024BB PR1
02.01.2024CC PR3
  
31.01.2024AA PR1
01.02.2024AA PR2
  
29.02.2024CC PR3
01.03.2024CC PR4
01.03.2024AA PR2

 

How to create last column "Product & Process"?

thanks 

2 ACCEPTED SOLUTIONS
bhanu_gautam
Super User
Super User

@KamilNewOnPBI , 

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
)

 




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

Anonymous
Not applicable

Hi @KamilNewOnPBI ,

 

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

vkaiyuemsft_0-1732759127158.png

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.

View solution in original post

3 REPLIES 3
KamilNewOnPBI
New Member

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

Anonymous
Not applicable

Hi @KamilNewOnPBI ,

 

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

vkaiyuemsft_0-1732759127158.png

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
Super User
Super User

@KamilNewOnPBI , 

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
)

 




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.