Forum Discussion

jimpatel's avatar
jimpatel
Post Patron
2 years ago
Solved

Pivot table logic

Thanks a lot for looking at my post   Any idea for below logic will be appreciated guys    1. I am looking for some sort of idea/logic for below ,    The logic is   a. I have order number, mate...
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    hi jimpatel ,

     

    supposing you have a data table like:

    Order Material Step Status
    2 225 1 Finished
    2 225 2 Finished
    2 225 3 Waiting
    2 225 4 Finished
    2 225 5 Waiting
    2 225 6 Waiting
    4 32 1 Waiting
    4 32 2 Finished
    4 32 3 Waiting
    8 225 1 Finished
    8 225 2 Finished
    8 225 3 Finished
    8 225 4 Finished
    8 225 5 Waiting
    8 225 6 Waiting
    9 3 1 Finished
    9 3 2 Waiting
    9 3 3 Finished
    9 3 4 Waiting
    9 3 5 Waiting

     

     

    try to plot a matrix visual with step column, material column and a measure like:

    measure = 
VAR _mat = MAX(data[material])
VAR _laststep =
MAXX(
    FILTER(
        ALL(data), 
        data[status] = "Finished"
            &&data[material]=_mat
    ),
    data[step]
)+1
VAR _result =
COUNTROWS(
    FILTER(
        ALL(data),
        data[step] =_laststep
            &&data[material] = _mat
    )
)
RETURN 
IF(
    MAX(data[Step]) = _laststep, 
    _result, ""
)

     

    it worked like:

     

jimpatel's avatar
jimpatel
Post Patron
2 years ago

I tried below code but still no luck 😞 to get below solution please

 

 

 

measure 1 = 
VAR _mat = MAX(data1[Material])     VAR _ord = MAX(data1[Order])
VAR _laststep =
MAXX(
    FILTER(
        ALL(data1), 
        data1[status] = "Finished"
            &&data1[Material]=_mat&&data1[Order]=_ord
    ),
    data1[step]
)+1
VAR _result =
COUNTROWS(
    FILTER(
        ALL(data1),
        data1[step] =_laststep
            &&data1[Material] = _mat
    )
)
RETURN 
IF(
    MAX(data1[Step]) = _laststep, 
    _result, ""
)