topic Re: Self-referencing calculated column to generated ascending number sequence that repeats in DAX Commands and Tips

Self-referencing calculated column to generated ascending number sequence that repeats

MorePowerBI — Thu, 21 Jul 2022 20:51:02 GMT

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

Re: Self-referencing calculated column to generated ascending number sequence that repeats

amitchandak — Fri, 22 Jul 2022 03:43:08 GMT

MorePowerBI , You can not self-reference, You need to think cumulative. You have to use logic earlier

example

Continuous streak: https://youtu.be/GdMcwvdwr0o

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1290346

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Self-referencing calculated column to generated ascending number sequence that repeats

MorePowerBI — Fri, 22 Jul 2022 17:24:15 GMT

amitchandak Yeah I figured I'll need to incorporate EARLIER() somehow. Here's the link to the files: OneDrive

Re: Self-referencing calculated column to generated ascending number sequence that repeats

MorePowerBI — Mon, 25 Jul 2022 15:31:30 GMT

Still looking to see if anyone can help out a fellow Power BI addict

Re: Self-referencing calculated column to generated ascending number sequence that repeats

vapid128 — Mon, 25 Jul 2022 18:51:46 GMT

It is all 13:02, Why there is 1 2 3 4 5?

Re: Self-referencing calculated column to generated ascending number sequence that repeats

vapid128 — Mon, 25 Jul 2022 19:01:41 GMT

I create a Line Number to rank this one.

Rank1 =

IF('Table (3)'[ValueName]="Dispense Weight Chec",

RANKX(

FILTER('Table (3)','Table (3)'[ValueName]<>"Dispense Weight Chec"),

'Table (3)'[LineNumber],

,ASC),

BLANK()

)

Rank2 =

IF('Table (3)'[ValueName]="Dispense Weight Chec",

RANKX(

FILTER('Table (3)',[Rank1]=EARLIER('Table (3)'[Rank1])),

[LineNumber],

,ASC

BLANK()

)

Re: Self-referencing calculated column to generated ascending number sequence that repeats

MorePowerBI — Mon, 25 Jul 2022 19:41:03 GMT

You wizard!