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 and status column

 b. The result i am looking for is arrowed below

Any help please?

Thanks a lot

[Anonymous]

Hi @jimpatel ,

I found out that you have another post with the same need and has already solved it.

Solved: Pivot table logic - Microsoft Fabric Community

Best Regards,

Xianda Tang

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

[jimpatel]

Any help much appreciated

thanks a lot

[jimpatel]

Please let me know if the explanation is not clear enough 

thanks a lot

[jimpatel]

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]

Any idea please

Much appreciated

Thanks

[FreemanZ]

hi @jimpatel ,

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

[jimpatel]

Any idea BI super users ?

Thanks

[jimpatel]

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

[jimpatel]

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]

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

[jimpatel]

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

[FreemanZ]

hi @jimpatel ,

1. the code shall still work

2. try to get the column in digit, ask your data source or process is first in Power Query, like this:

https://monocroft.com/extract-numbers-from-a-string-in-power-bi/

[jimpatel]

Any idea please?

Thanks a lot 

[jimpatel]

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

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]

For some reason i am getting blank data 

Thanks a lot

[FreemanZ]

hi @jimpatel ,

Could you explain the logic reaching the expected result?