Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
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
)
Proud to be a Super User! |
|
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
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.
This was really good help and hints for DAX functions. Thanks
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
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.
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
)
Proud to be a Super User! |
|
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
6 |