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 NumTo Part
CCXCVC7XTN
CCXCVP5YFN
CCXCVTNTCT

 

to this:

 

Part NumDev1Dev2Dev3
CCXCVC7XTNP5YFNTNTCT
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] )
            )
        )
)

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a new table by using DAX.

     

     

    New Table = 
ADDCOLUMNS (
    VALUES ( Data[Part Num] ),
    "Dev1",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX ( 1, SUMMARIZE ( Data, Data[To Part] ), ORDERBY ( Data[To Part] ) )
        ),
    "Dev2",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX ( 2, SUMMARIZE ( Data, Data[To Part] ), ORDERBY ( Data[To Part] ) )
        ),
    "Dev3",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX ( 3, SUMMARIZE ( Data, 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] )
            )
        )
)