cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ratercero Member
Member

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

Accepted Solutions
Super User
Super User

Re: Filter batch # by earlier date of status

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

Re: Filter batch # by earlier date of status

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
6 REPLIES 6
Super User
Super User

Re: Filter batch # by earlier date of status

Hey,

 

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

 

Regards

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
ratercero Member
Member

Re: Filter batch # by earlier date of status

ratercero Member
Member

Re: Filter batch # by earlier date of status

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

Super User
Super User

Re: Filter batch # by earlier date of status

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
ratercero Member
Member

Re: Filter batch # by earlier date of status

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

 

 

Super User
Super User

Re: Filter batch # by earlier date of status

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 43 members 1,160 guests
Please welcome our newest community members: