Filter batch # by earlier date of status

[ratercero]

Hello,

Im trytin to determined the description of a batch when fist entered a status,

I have been trying to use "earliest" and "min" functions but returns errors, this the the code the got me the closest"

1ST_REPRO = CALCULATE(
	FIRSTNONBLANK(
		t_Batch[DESCRIPTION],
		t_Batch[DESCRIPTION]),
		CALCULATETABLE(
			t_Batch,
			ALLEXCEPT(t_Batch,
				t_Batch[BATCH_NUMBER_1]),
				t_Batch[BATCH_PROCESS]="REPRO"))

The column should return "RP" in the example but is returning "MT" instead:

 

The table also has a unique sequential productionID.

Thanks

[TomMartens · Accepted solution]

Hey,

I have 🙂

The output from this measure (added to your table "t_batch")

AMeasure = 
var currentBatchNumber = if(HASONEVALUE('t_Batch'[BATCH_NUMBER_1]), VALUES('t_Batch'[BATCH_NUMBER_1]), BLANK())
var minSequenceNumberOfBatch_eq_REPRO =CALCULATE(
	MIN('t_Batch'[PROCESS_SEQUENCE]
	)
	,ALLEXCEPT('t_Batch','t_Batch'[BATCH_NUMBER_1])
	,'t_Batch'[BATCH_PROCESS]="REPRO"
)
return
IF(NOT(ISBLANK(currentBatchNumber))
	,LOOKUPVALUE('t_Batch'[DESCRIPTION],'t_Batch'[BATCH_NUMBER_1],currentBatchNumber, 't_Batch'[PROCESS_SEQUENCE], minSequenceNumberOfBatch_eq_REPRO)
	,"SELECT one BatchNumber")

 creates this output

What the measure does

1. the current selected batch_number_1 is stored into a variable
2. the minimum process_sequence number for the selected batch_number_1 where the batch_process equals "REPRO" is stored in a variable
3. Both variables are used inside the LOOKUPVALUE() function to retrieve the "DESCRIPTION"

I have to admit that I encountered some problems using other approaches, stemming from calculated columns I wanted to use in FILTER expressions. The issues I encountered were due to BLANK() values in these columne resulting in an errer message from Power BI, so this solution may not be the speediest one, but w/o deeper knowledge of your model a "good" one.

Hope this helps

Cheers

[TomMartens]

Hey,

can you please share your sample data, provide a link to download a pbix from onedrive or dropbox.

Regards

[ratercero]

@TomMartens here it is:

https://www.dropbox.com/s/82hgr706y0xwu10/SedoMaster%20link%20E.pbix?dl=0

Thanks in advance

[ratercero]

Hello @TomMartens, were you able to figure this out?

[TomMartens · Accepted solution]

Hey,

I have 🙂

The output from this measure (added to your table "t_batch")

AMeasure = 
var currentBatchNumber = if(HASONEVALUE('t_Batch'[BATCH_NUMBER_1]), VALUES('t_Batch'[BATCH_NUMBER_1]), BLANK())
var minSequenceNumberOfBatch_eq_REPRO =CALCULATE(
	MIN('t_Batch'[PROCESS_SEQUENCE]
	)
	,ALLEXCEPT('t_Batch','t_Batch'[BATCH_NUMBER_1])
	,'t_Batch'[BATCH_PROCESS]="REPRO"
)
return
IF(NOT(ISBLANK(currentBatchNumber))
	,LOOKUPVALUE('t_Batch'[DESCRIPTION],'t_Batch'[BATCH_NUMBER_1],currentBatchNumber, 't_Batch'[PROCESS_SEQUENCE], minSequenceNumberOfBatch_eq_REPRO)
	,"SELECT one BatchNumber")

 creates this output

What the measure does

1. the current selected batch_number_1 is stored into a variable
2. the minimum process_sequence number for the selected batch_number_1 where the batch_process equals "REPRO" is stored in a variable
3. Both variables are used inside the LOOKUPVALUE() function to retrieve the "DESCRIPTION"

I have to admit that I encountered some problems using other approaches, stemming from calculated columns I wanted to use in FILTER expressions. The issues I encountered were due to BLANK() values in these columne resulting in an errer message from Power BI, so this solution may not be the speediest one, but w/o deeper knowledge of your model a "good" one.

Hope this helps

Cheers

[ratercero]

@TomMartens,

this worked when only one batch is filtered, I'm looking to have it stored in each row because it is goint to be used on a condition to make categories.

So it is close to what im looking for but not exactly.

[TomMartens · Accepted solution]

Hey,

not sure if you already figured this out, here is a measure that works if more than BATCH_NUMBER_1 is selected

AAMeasure = 
var currentBatchNumber = calculate(MAXX(VALUES('t_Batch'[BATCH_NUMBER_1]),'t_Batch'[BATCH_NUMBER_1]))
var minStartedDateOfBatch_eq_REPRO =
CALCULATE(
	min('t_Batch'[c_StartedDT])
	,FILTER(ALL('t_Batch')
	,'t_Batch'[BATCH_NUMBER_1] = currentBatchNumber && 't_Batch'[BATCH_PROCESS]="REPRO")
	)
return
//currentBatchNumber
//minSequenceNumberOfBatch_eq_REPRO
IF(HASONEVALUE('t_Batch'[BATCH_NUMBER_1])
,CALCULATE(LOOKUPVALUE('t_Batch'[DESCRIPTION],'t_Batch'[BATCH_NUMBER_1],currentBatchNumber, 't_Batch'[c_StartedDT], minStartedDateOfBatch_eq_REPRO))
,BLANK())

 And here is a little screenshot

Guess we're there yet 😉

Regards