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 (might not be in sequence and sometimes it will jump) and status column

 b. The result i am looking for is arrowed below

 

 

The code currently what i am having is below but does not work as i needed please 

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

 

 

Any help please?

 

Thanks a lot

  • 123abc's avatar
    123abc
    2 years ago

    You can use the following DAX formula to achieve this:

     

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

    This formula calculates the maximum step number of a material number that has a status of “Finished”. It then adds 1 to this number to get the last step. Finally, it counts the number of rows that have this last step and the same material number. If the maximum step number in the data is equal to the last step, the formula returns the count of rows with the last step and the same material number. Otherwise, it returns an empty string.

    I hope this helps. Let me know if you have any further questions

     

  • 123abc's avatar
    123abc
    2 years ago

    If some of the results are showing empty strings instead of count numbers, it could be because the COUNTROWS function is returning a blank value. To handle this situation, you can use the IF and ISBLANK functions to check if the COUNTROWS function is returning a blank value and then return 0 instead of an empty string. Here is an example of how you can modify the formula:

    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, 
    IF(ISBLANK(_result), 0, _result), 
    ""
)

    I hope this helps! Let me know if you have any further questions.

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

     

    In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

9 Replies

  • 123abc's avatar
    123abc
    Community Champion
    2 years ago

    Your DAX code looks close, but there might be some adjustments needed. Below is an example of how you might modify your code:

     

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

     

    Changes made:

    1. I replaced MAXX with CALCULATE around the _result variable to make sure the context transition is preserved.
    2. I replaced "" with BLANK() in the IF statement.

    Please replace "data" with your actual table name if it's different. Also, make sure that your column names (e.g., "material," "status," "step") match your actual column names.

    If you encounter any issues or if the logic is still not working as expected, please provide more details about the structure of your data and any specific error messages you are encountering.

  • jimpatel's avatar
    jimpatel
    Post Patron
    2 years ago

    Perfect thanks a lot and much appreciated for your help.

     

    Just one question, how to modify the code to get "Step +1" as the count please?

     

    Thanks a lot again

    • 123abc's avatar
      123abc
      Community Champion
      2 years ago

      Sure, if you want to count the rows where the step is the next step after the last finished step for each material, you can modify the code like this:

       

      NextStepCount =
      CALCULATE(
      COUNTROWS(data),
      FILTER(
      ALL(data),
      data[Material] = EARLIER(data[Material]) &&
      data[Step] = EARLIER(data[Step]) + 1 &&
      data[Status] = "Finished"
      )
      )NextStepCount =
      CALCULATE(
      COUNTROWS(data),
      FILTER(
      ALL(data),
      data[Material] = EARLIER(data[Material]) &&
      data[Step] = EARLIER(data[Step]) + 1 &&
      data[Status] = "Finished"
      )
      )

       

      In this code, I replaced the condition data[Step] = CALCULATE(MAX(data[Step]), ...) with data[Step] = EARLIER(data[Step]) + 1 to check if the step is the next step after the last finished step.

      Now, the measure NextStepCount will count the rows where the step is one greater than the last finished step for each material.

      Adjust this code based on your specific requirements and let me know if you have any more questions!

       

      If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

       

      In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

  • jimpatel's avatar
    jimpatel
    Post Patron
    2 years ago

    Thanks a lot for your reply,  really sorry to bother you again, is that mean i need to update the code like below?

    Plus code is not letting me to put Material next to Earlier please

     

    Measure =
VAR _mat = MAX(data[material])
VAR _laststep =
MAXX(
FILTER(
ALL(data),
data[status] = "Finished"
&& data[material] = _mat
),
data[step]
)
VAR _result =
CALCULATE(
COUNTROWS(data),
FILTER(
ALL(data),
data[Material] = EARLIER(data[Material]) &&
data[Step] = EARLIER(data[Step]) + 1 &&
data[Status] = "Finished"
)
)
RETURN
IF(
MAX(data[Step]) = _laststep,
_result,
BLANK()
)

     

    • 123abc's avatar
      123abc
      Community Champion
      2 years ago

      You can use the following DAX formula to achieve this:

       

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

      This formula calculates the maximum step number of a material number that has a status of “Finished”. It then adds 1 to this number to get the last step. Finally, it counts the number of rows that have this last step and the same material number. If the maximum step number in the data is equal to the last step, the formula returns the count of rows with the last step and the same material number. Otherwise, it returns an empty string.

      I hope this helps. Let me know if you have any further questions

       

      • jimpatel's avatar
        jimpatel
        Post Patron
        2 years ago

        Sorry for opening topic again,

         

        For some of them its showing empty string rather than count number. 

         

        Any idea please?

         

        Thanks a lot

         

        Suppose to be 1 in the below highlighed area but showing empty string

         

         

         

        Thanks

  • jimpatel's avatar
    jimpatel
    Post Patron
    2 years ago

    Perfect much appreciated for your help 

     

    Thanks a lot

     

    Jim