Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jimpatel
Helper V
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

 

jimpatel_0-1700733284359.png

 

Any help please?

 

Thanks a lot

1 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:

FreemanZ_0-1700747203561.png

 

View solution in original post

17 REPLIES 17
v-xiandat-msft
Community Support
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.

jimpatel
Helper V
Helper V

Any help much appreciated

 

thanks a lot

jimpatel
Helper V
Helper V

Please let me know if the explanation is not clear enough 

 

thanks a lot

jimpatel
Helper V
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  

jimpatel_0-1701072259280.png

jimpatel_1-1701072536526.png

Much appreciated again

 

 

 

 

jimpatel
Helper V
Helper V

Any idea please

 

Much appreciated

 

Thanks

hi @jimpatel ,

 

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

jimpatel
Helper V
Helper V

Any idea BI super users ?

 

Thanks

jimpatel
Helper V
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, ""
)

 

 

jimpatel_0-1700757148782.png

 

jimpatel_1-1700757156705.png

 

 

 

jimpatel
Helper V
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

 

jimpatel_3-1700753914323.png

 

jimpatel_4-1700753954173.png

 

Because those order numbers are different

 

Thanks a lot for your kind help

 

jimpatel
Helper V
Helper V

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

jimpatel
Helper V
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

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/

 

Any idea please?

 

Thanks a lot 

jimpatel
Helper V
Helper V

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

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:

FreemanZ_0-1700747203561.png

 

For some reason i am getting blank data 

 

Thanks a lot

FreemanZ
Super User
Super User

hi @jimpatel ,

 

Could you explain the logic reaching the expected result?

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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