How to capture a step in a production line using DAX

HenryG — Sat, 03 Feb 2024 00:51:09 GMT

Hi guys,
I need help with something, I'm doing a project about a production line dashboard and I'm having some issues.

In this case, I have a lot of steps in this production line and the main objective here is to capture one specific moment in the middle of the process.

Here is an example of the data I'm using:

Let's say I need to capture the first moment in this process that the product passes by step 3 and consider a value for them, and the other steps I don't consider any values, and if the product gets back for step 2, I don't consider any value if they pass again for the step 3, only the first time and save the date of the first time this product passes by the step 3.

But I can't use a Power Query to do it, because I'm using a Dynamic Connection with the data in this dashboard. It needs to be done using a Measure.

And I have some conditions depending on the product.

Example: If the product is Red I need to capture it in step 3, but if the product is Blue I need to capture the moment they pass for step 2.

Can you guys help me with this?

Thank you.

Re: How to capture a step in a production line using DAX

123abc — Sat, 03 Feb 2024 05:45:03 GMT

To capture a specific moment in a production line using DAX (Data Analysis Expressions), you can create a measure that calculates the date and time when a product passes through a particular step based on certain conditions. Here's how you can achieve this:

Assuming you have a table named ProductionData containing the production line data with columns like Product, Step, and Timestamp, you can create a measure to capture the moment when a product passes through a specific step.

Here's a general structure of how you might implement the measure:

Capture Moment =
VAR CurrentProduct = SELECTEDVALUE('ProductionData'[Product])
VAR CurrentStep = SELECTEDVALUE('ProductionData'[Step])
VAR CaptureStep =
IF(
CurrentProduct = "Red" && CurrentStep = 3,
1,
IF(
CurrentProduct = "Blue" && CurrentStep = 2,
1,
0
)
)
RETURN
IF(
CaptureStep = 1,
MINX(
FILTER(
'ProductionData',
'ProductionData'[Product] = CurrentProduct &&
'ProductionData'[Step] = CurrentStep
),
'ProductionData'[Timestamp]
),
BLANK()
)

Explanation:

The measure starts by defining variables for the current product and step based on the selected values in your report.
It then evaluates the conditions based on the product type and step number to determine if the moment needs to be captured (CaptureStep variable).
If the conditions are met, it uses the MINX function to find the minimum timestamp for the selected product and step combination.
If the conditions are not met, it returns BLANK().

You can adjust this measure according to your actual column names and conditions. Make sure to replace 'ProductionData' with the actual name of your table.

You can then use this measure in your dashboard to display the captured moments for each product based on the conditions specified.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

topic How to capture a step in a production line using DAX in DAX Commands and Tips

How to capture a step in a production line using DAX

Re: How to capture a step in a production line using DAX