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, material number, steps and status column

 b. The result i am looking for is arrowed below

 

 

Any help please?

 

Thanks a lot

  • 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:

     

17 Replies

  • jimpatel's avatar
    jimpatel
    Post Patron
    2 years ago

    Thanks a lot for your reply

     

    The logic is, For order number 2 and material number 225  - solution (step 5 will be 2) because all step upto 4 is finished (Even though step 3 showing waiting but we can ignore as we are in step 4) , so next step will step 5 , same concept for order number 8 for same material number. 

     

    Please let me know if this is not clear

     

    Thanks again

    • FreemanZ's avatar
      FreemanZ
      Super User
      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

        For some reason i am getting blank data 

         

        Thanks a lot

  • jimpatel's avatar
    jimpatel
    Post Patron
    2 years ago

    Thanks a lot for your great effort. Couple of thing is

    1. i have material sometimes start with Alphabets and therefore i have in Text format.

    2. My status is derived from DAX formula as well , 

    Is there anything i can change the code to make it work please?

     

    Thanks a lot again

  • jimpatel's avatar
    jimpatel
    Post Patron
    2 years ago

    Thanks a lot. Just found its working for some of the material numbers and some of them it does not 😞

  • jimpatel's avatar
    jimpatel
    Post Patron
    2 years ago

    I found the issue, I have attached another scenario with updated data. Those data are not visible in the result. Any idea pleasE?

     

    Thanks a lot

    I cant find the attachment symbol and apologies for the below screenshot

     

     

     

    Because those order numbers are different

     

    Thanks a lot for your kind help

     

  • 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, ""
)

     

     

     

     

     

     

    • FreemanZ's avatar
      FreemanZ
      Super User
      2 years ago

      hi jimpatel ,

       

      what issue do you encounter? btw, your screenshot is too small and hardly visible. 

  • jimpatel's avatar
    jimpatel
    Post Patron
    2 years ago

    Thanks a lot for your reply. Currently the code is looking for each material number the highest step and counting it. May i know how to check for each order number and material number together please? that is in this example i have added order 3 for material 225 and order 7 for material 32 and i would like to see the answer as below please . ? really much appreciated your help  

    Much appreciated again

     

     

     

     

  • jimpatel's avatar
    jimpatel
    Post Patron
    2 years ago

    Please let me know if the explanation is not clear enough 

     

    thanks a lot