Forum Discussion

srineo's avatar
srineo
Regular Visitor
8 years ago
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 ...
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    srineo,

     

    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" )
v-chuncz-msft's avatar
v-chuncz-msft
Community Support
8 years ago

srineo,

 

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" )
srineo's avatar
srineo
Regular Visitor
8 years ago

Thanks Sam, it worked.