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

FreemanZ

2 years ago

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: