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.

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.