Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ratercero
Helper III
Helper III

Filter batch # by earlier date of status

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:

 Capture.PNG

 

The table also has a unique sequential productionID.

 

Thanks

 

2 ACCEPTED SOLUTIONS

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
2017-08-16_18-27-03.png

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

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
Multiple Batch Number.png

 

Guess we're there yet 😉

 

Regards



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

6 REPLIES 6
TomMartens
Super User
Super User

Hey,

 

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

 

Regards



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

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
2017-08-16_18-27-03.png

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@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.

 

 

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
Multiple Batch Number.png

 

Guess we're there yet 😉

 

Regards



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.