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.
I need to create a pair of calculated columns that return the next and previous TRUE_MACH_NO of each grouped ORDER_NO & SPEC_NO determined by MACH_SEQ_NO. Sometimes the TRUE_MACH_NO is the same twice in a row because the product does have to go through the same machine twice, but this is reflected in the table as an incremented MACH_SEQ_NO. The MACH_SEQ_NO sometimes has decimals (I don't know why) and sometimes even goes negative (I REALLY don't know why)...
SPEC_NO | MACH_SEQ_NO | TRUE_MACH_NO | PREV_MACH | NEXT_MACH |
556927 | 1 | 23 | null | 64 |
556927 | 2 | 64 | 23 | 64 |
556927 | 3 | 64 | 64 | 3001 |
556927 | 4 | 3001 | 64 | 3001 |
556927 | 5 | 3001 | 3001 | 900 |
556927 | 6 | 900 | 3001 | null |
Hi @dpiechowski
try
Prev_Mach =
var _curMACH_SEQ_NO = [MACH_SEQ_NO]
var _prevMACH_SEQ_NO = CALCULATE(MAX(Table[MACH_SEQ_NO], ALLEXCEPT(Table, Table[ORDER_NO], Table[SPEC_NO]), Table[MACH_SEQ_NO] < _curMACH_SEQ_NO )
RETURN
CALCULATE(MAX(Table[TRUE_MACH_NO]), ALLEXCEPT(Table, Table[ORDER_NO], Table[SPEC_NO]), Table[MACH_SEQ_NO] = _prevMACH_SEQ_NO)
and
Prev_Mach =
var _curMACH_SEQ_NO = [MACH_SEQ_NO]
var _nextMACH_SEQ_NO = CALCULATE(MIN(Table[MACH_SEQ_NO], ALLEXCEPT(Table, Table[ORDER_NO], Table[SPEC_NO]), Table[MACH_SEQ_NO] > _curMACH_SEQ_NO )
RETURN
CALCULATE(MAX(Table[TRUE_MACH_NO]), ALLEXCEPT(Table, Table[ORDER_NO], Table[SPEC_NO]), Table[MACH_SEQ_NO] = _nextMACH_SEQ_NO)
Thank you. I think this is the right way to go, but I can't seem to get the syntax correct. It says 'Unexpected Parameter' and I'm not quite sure how to adjust it to clear the error.
here are some examples of possible solutions.
Prev =
var sn='Table (4)'[SPEC_NO]
var ms='Table (4)'[MACH_SEQ_NO]-1
var m=CALCULATE(max('Table (4)'[TRUE_MACH_NO]),all('Table (4)'),treatas({(sn,ms)},'Table (4)'[SPEC_NO],'Table (4)'[MACH_SEQ_NO]))
return m
Next =
var sn='Table (4)'[SPEC_NO]
var ms='Table (4)'[MACH_SEQ_NO]+1
var m=CALCULATE(max('Table (4)'[TRUE_MACH_NO]),all('Table (4)'),'Table (4)'[SPEC_NO]=sn,'Table (4)'[MACH_SEQ_NO]=ms)
return m
This seems to work really well except for the non-integer MACH_SEQ_NO rows, since var ms is subtracting 1 from itself it will miss any non-standard steps in the sequence. This is a good start though! Thank you.
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |