Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |