Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm trying to recreate the highlighted [Dispense #] column in the JMP table.
The column generates an ascending number sequence for each consecutive row with [ValueName] = "Dispense Weight Chec". The number sequence restarts at 1 if the previous row's value for [ValueName]<>"Dispense Weight Chec".
The JMP code for the column is:
If( :ValueName == "Dispense Weight Chec", If( Lag( :ValueName ) != "Dispense Weight Chec", 1, Lag( :Dispense # ) + 1 )
Not sure if PBI allows calculated columns to self-reference its previous row's value, but that seems to be what I need to generate this column.
Here's the sample dataset:
Site | ProcessName | EQNumber | ValueName | Value | CreatedDateTime | WC Type | WC Result | Dispense # |
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 3.599609375 | 7/17/2022 12:50 | Tip Change | 1 | |
SAN | TED Station | EQ-901848-004 | DispenseTipXOffset | -0.062082361 | 7/17/2022 12:53 | Tip Change | ||
SAN | TED Station | EQ-901848-004 | DispenseTipYOffset | -0.364204079 | 7/17/2022 12:53 | Tip Change | ||
SAN | TED Station | EQ-901848-004 | DispenseTipZOffset | 1.637206554 | 7/17/2022 12:53 | Tip Change | ||
SAN | TED Station | EQ-901848-004 | DispenseTipXOffset | -0.136351258 | 7/17/2022 12:59 | Tip Change | ||
SAN | TED Station | EQ-901848-004 | DispenseTipYOffset | -0.399612635 | 7/17/2022 12:59 | Tip Change | ||
SAN | TED Station | EQ-901848-004 | DispenseTipZOffset | 0.958335042 | 7/17/2022 12:59 | Tip Change | ||
SAN | TED Station | EQ-901848-004 | DispenseTipXOffset | -0.129197747 | 7/17/2022 13:02 | Tip Change | ||
SAN | TED Station | EQ-901848-004 | DispenseTipYOffset | -0.308497012 | 7/17/2022 13:02 | Tip Change | ||
SAN | TED Station | EQ-901848-004 | DispenseTipZOffset | 1.200035095 | 7/17/2022 13:02 | Tip Change | ||
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 2.899414063 | 7/17/2022 13:02 | Tip Change | 1 | |
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 3.900390625 | 7/17/2022 13:02 | Tip Change | 2 | |
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 4.099609375 | 7/17/2022 13:02 | Tip Change | 3 | |
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 3.900390625 | 7/17/2022 13:02 | Tip Change | 4 | |
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 3.799804688 | 7/17/2022 13:02 | Tip Change | 5 | |
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 4.200195313 | 7/17/2022 13:03 | Tip Change | 6 | |
SAN | TED Station | EQ-901848-004 | Calculated Flow Rate | 17.53338432 | 7/17/2022 13:03 | Tip Change | ||
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 4.099609375 | 7/17/2022 13:14 | Inline | Pass | 1 |
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 3.900390625 | 7/17/2022 13:14 | Inline | Pass | 2 |
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 4.200195313 | 7/17/2022 13:14 | Inline | Pass | 3 |
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 4 | 7/17/2022 13:14 | Inline | Pass | 4 |
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 4.200195313 | 7/17/2022 13:15 | Inline | Pass | 5 |
SAN | TED Station | EQ-901848-004 | Calculated Flow Rate | 17.89508057 | 7/17/2022 13:15 | Inline | ||
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 3.899414063 | 7/17/2022 13:25 | Inline | Pass | 1 |
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 4.100585938 | 7/17/2022 13:25 | Inline | Pass | 2 |
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 4 | 7/17/2022 13:25 | Inline | Pass | 3 |
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 4 | 7/17/2022 13:26 | Inline | Pass | 4 |
SAN | TED Station | EQ-901848-004 | Dispense Weight Chec | 4.099609375 | 7/17/2022 13:26 | Inline | Pass | 5 |
SAN | TED Station | EQ-901848-004 | Calculated Flow Rate | 17.94607925 | 7/17/2022 13:26 | Inline |
Solved! Go to Solution.
I create a Line Number to rank this one.
It is all 13:02, Why there is 1 2 3 4 5?
I create a Line Number to rank this one.
You wizard!
Still looking to see if anyone can help out a fellow Power BI addict
@MorePowerBI , You can not self-reference, You need to think cumulative. You have to use logic earlier
example
Continuous streak: https://youtu.be/GdMcwvdwr0o
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
@amitchandak Yeah I figured I'll need to incorporate EARLIER() somehow. Here's the link to the files: OneDrive
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |