topic Re: Pivot table logic in DAX Commands and Tips

Pivot table logic

jimpatel — Thu, 23 Nov 2023 09:55:05 GMT

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

Re: Pivot table logic

FreemanZ — Thu, 23 Nov 2023 12:39:02 GMT

hi jimpatel ,

Could you explain the logic reaching the expected result?

Re: Pivot table logic

jimpatel — Thu, 23 Nov 2023 12:43:12 GMT

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

Re: Pivot table logic

FreemanZ — Thu, 23 Nov 2023 13:47:54 GMT

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:

Re: Pivot table logic

jimpatel — Thu, 23 Nov 2023 14:12:13 GMT

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

Re: Pivot table logic

jimpatel — Thu, 23 Nov 2023 14:28:06 GMT

For some reason i am getting blank data

Thanks a lot

Re: Pivot table logic

FreemanZ — Thu, 23 Nov 2023 14:35:53 GMT

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/

Re: Pivot table logic

jimpatel — Thu, 23 Nov 2023 15:10:05 GMT

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

Re: Pivot table logic

jimpatel — Thu, 23 Nov 2023 15:39:37 GMT

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

Re: Pivot table logic

jimpatel — Fri, 24 Nov 2023 12:20:46 GMT

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

Re: Pivot table logic

jimpatel — Fri, 24 Nov 2023 07:59:24 GMT

Any idea please?

Thanks a lot

Re: Pivot table logic

jimpatel — Fri, 24 Nov 2023 12:21:52 GMT

Any idea BI super users ?

Thanks

Re: Pivot table logic

jimpatel — Sun, 26 Nov 2023 19:39:28 GMT

Any idea please

Much appreciated

Thanks

Re: Pivot table logic

FreemanZ — Mon, 27 Nov 2023 01:34:00 GMT

hi jimpatel ,

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

Re: Pivot table logic

jimpatel — Mon, 27 Nov 2023 08:09:30 GMT

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

Re: Pivot table logic

jimpatel — Mon, 27 Nov 2023 12:06:22 GMT

Please let me know if the explanation is not clear enough

thanks a lot

Re: Pivot table logic

jimpatel — Tue, 28 Nov 2023 18:24:40 GMT

Any help much appreciated

thanks a lot

Re: Pivot table logic

Anonymous — Wed, 06 Dec 2023 01:32:22 GMT

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.