Pivot table logic

[jimpatel]

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 · Accepted solution]

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]

Perfect much appreciated for your help 

Thanks a lot

Jim

[jimpatel]

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 · Accepted solution]

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]

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

[123abc · Accepted solution]

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]

Perfect and much appreciated your help

thanks again

[jimpatel]

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]

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.

[123abc]

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.