cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper V

## 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, material number, steps and status column

b. The result i am looking for is arrowed below

Thanks a lot

1 ACCEPTED SOLUTION
Super User

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:

17 REPLIES 17
Community Support

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.

Helper V

Any help much appreciated

thanks a lot

Helper V

Please let me know if the explanation is not clear enough

thanks a lot

Helper V

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

Helper V

Much appreciated

Thanks

Super User

hi @jimpatel ,

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

Helper V

Any idea BI super users ?

Thanks

Helper V

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

Helper V

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

Helper V

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

Helper V

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

Super User

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/

Helper V

Thanks a lot

Helper V

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

Super User

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:

Helper V

For some reason i am getting blank data

Thanks a lot

Super User

hi @jimpatel ,

Could you explain the logic reaching the expected result?

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors