Forum Discussion
Solved
Logic in M query
Hi all,
I'm looking for help in coming with a logic in M query or DAX for below problem. Based on Assignment and Pallet ID - I would need to fill Break Indicator. With in a assignment at the end of every second pallet ID i would need to fill 'X' in break Ind column.
If no second pallet but assignment end line reached then mark X (last line in assignment '1' ).
| Assignment | Pallet ID | Break Ind |
| 1 | 1 | |
| 1 | 1 | |
| 1 | 1 | |
| 1 | 2 | X |
| 1 | 3 | |
| 1 | 3 | X |
| 2 | 4 | |
| 2 | 4 | |
| 2 | 5 | |
| 2 | 5 | X |
| 2 | 6 | |
| 2 | 6 | |
| 2 | 7 | X |
Thanks,
Sri
You may follow the steps below.
1) add an index column in Query Editor
2) add a calculated column in DAX
Column = VAR m = MAXX ( FILTER ( Table1, Table1[Assignment] = EARLIER ( Table1[Assignment] ) ), Table1[Index] ) VAR f = LOOKUPVALUE ( Table1[Pallet ID], Table1[Assignment], Table1[Assignment], Table1[Index], Table1[Index] + 1 ) = Table1[Pallet ID] VAR r = RANKX ( FILTER ( Table1, Table1[Assignment] = EARLIER ( Table1[Assignment] ) ), Table1[Pallet ID], , ASC, DENSE ) RETURN IF ( Table1[Index] = m || ( f = FALSE () && MOD ( r, 2 ) = 0 ), "X" )
2 Replies
You may follow the steps below.
1) add an index column in Query Editor
2) add a calculated column in DAX
Column = VAR m = MAXX ( FILTER ( Table1, Table1[Assignment] = EARLIER ( Table1[Assignment] ) ), Table1[Index] ) VAR f = LOOKUPVALUE ( Table1[Pallet ID], Table1[Assignment], Table1[Assignment], Table1[Index], Table1[Index] + 1 ) = Table1[Pallet ID] VAR r = RANKX ( FILTER ( Table1, Table1[Assignment] = EARLIER ( Table1[Assignment] ) ), Table1[Pallet ID], , ASC, DENSE ) RETURN IF ( Table1[Index] = m || ( f = FALSE () && MOD ( r, 2 ) = 0 ), "X" )Thanks Sam, it worked.
