Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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
Hey,
can you please share your sample data, provide a link to download a pbix from onedrive or dropbox.
Regards
@TomMartens here it is:
https://www.dropbox.com/s/82hgr706y0xwu10/SedoMaster%20link%20E.pbix?dl=0
Thanks in advance
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
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
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
Guess we're there yet 😉
Regards
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |