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

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.