Forum Discussion

dmcmillin's avatar
dmcmillin
Regular Visitor
3 years ago
Solved

Table Array

Hi All,   I have tried using pivot and unpivot and transposing but can't quite figure out how to transform my table from this:   Any help would be appreciated   Part Num To Part CCXCV C...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi, please check the below picture and the attached pbix file.

    I am not sure how it is defined to understand what comes for Dev1 / Dev2 / Dev3, but I hope you get the ideas on how to create the solution for your business logic.

     

     

    New Table =
ADDCOLUMNS (
    VALUES ( Data[Part Num] ),
    "Dev1",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX (
                1,
                SUMMARIZE (
                    FILTER ( Data, Data[Part Num] = EARLIER ( Data[Part Num] ) ),
                    Data[To Part]
                ),
                ORDERBY ( Data[To Part] )
            )
        ),
    "Dev2",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX (
                2,
                SUMMARIZE (
                    FILTER ( Data, Data[Part Num] = EARLIER ( Data[Part Num] ) ),
                    Data[To Part]
                ),
                ORDERBY ( Data[To Part] )
            )
        ),
    "Dev3",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX (
                3,
                SUMMARIZE (
                    FILTER ( Data, Data[Part Num] = EARLIER ( Data[Part Num] ) ),
                    Data[To Part]
                ),
                ORDERBY ( Data[To Part] )
            )
        )
)

     

dmcmillin's avatar
dmcmillin
Regular Visitor
3 years ago

Jihwan_Kim you understood the concept. Thank you.

 

This is very odd, and may be because I am a new user but I can't reply with a .pbix file like you did.

 

I am unable to get what you produced to work and might be because I provided partial data. Here is a larger sample size which has different parts and different deviations that would have to be transposed:

 

Part NumTo Part
CCR41JW30H
CCXCVC7XTN
CCXCVP5YFN
CCXCVTNTCT
CC0F0G6R99
CC0F056D99
CDDMJXX7MT
CF4C1CC0F0
CGNVH6KNWW
CG0M0K18NP
CG48D0W8VV
CJMMK6FDH8
CJX0FWV4KK
CMFND6M9N4
CMPGMY26KX
CM9TJX3PK1
CPFWHWCF6J
CPFWH2N6C5
CP6106JKXY
CR72X68ND3
CTWCNC8T2P
CTWCNMGPPC

 

to look like this:

 

Part NumDev1Dev2Dev3
CCR41JW30H  
CCXCVC7XTNP5YFNTNTCT
CC0F0G6R9956D99 
CDDMJXX7MT  
CF4C1CC0F0  
CGNVH6KNWW  
CG0M0K18NP  
CG48D0W8VV  
CJMMK6FDH8  
CJX0FWV4KK  
CMFND6M9N4  
CMPGMY26KX  
CM9TJX3PK1  
CPFWHWCF6J2N6C5 
CP6106JKXY  
CR72X68ND3  
CTWCNC8T2PMGPPC 
Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

Hi, please check the below picture and the attached pbix file.

I am not sure how it is defined to understand what comes for Dev1 / Dev2 / Dev3, but I hope you get the ideas on how to create the solution for your business logic.

 

 

New Table =
ADDCOLUMNS (
    VALUES ( Data[Part Num] ),
    "Dev1",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX (
                1,
                SUMMARIZE (
                    FILTER ( Data, Data[Part Num] = EARLIER ( Data[Part Num] ) ),
                    Data[To Part]
                ),
                ORDERBY ( Data[To Part] )
            )
        ),
    "Dev2",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX (
                2,
                SUMMARIZE (
                    FILTER ( Data, Data[Part Num] = EARLIER ( Data[Part Num] ) ),
                    Data[To Part]
                ),
                ORDERBY ( Data[To Part] )
            )
        ),
    "Dev3",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX (
                3,
                SUMMARIZE (
                    FILTER ( Data, Data[Part Num] = EARLIER ( Data[Part Num] ) ),
                    Data[To Part]
                ),
                ORDERBY ( Data[To Part] )
            )
        )
)

 

  • dmcmillin's avatar
    dmcmillin
    Regular Visitor
    3 years ago

    Jihwan_Kim Thank you! I will make this work.