Forum Discussion

chefe's avatar
chefe
Icon for Helper II rankHelper II
10 years ago
Solved

how to pivot a table in-memory? using summarize+addcolumns?

Hello   A certain record (id = "#1") ahs to numeric values stored in separates lines, c.f. screenshot. How can pivot/merge/join these two lines to so that the result is a 1-line-table stored in-mem...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    10 years ago

    Hi Chefe,

     

    According to your description, you need to create a new table based on the exist table, right?

     

    I have tested it on my local environment, here is the sample DAX for you reference.

    Create a table
    NewTable = SUMMARIZE('Summary',Summary[ID])
    Create columns
    Amount1 = LOOKUPVALUE('Summary'[Amount],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg1")
    Unit1 = LOOKUPVALUE('Summary'[Unit],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg1")
    Amount2 = LOOKUPVALUE('Summary'[Amount],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg2")
    Unit2 = LOOKUPVALUE('Summary'[Unit],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg2")
    Amount3 = LOOKUPVALUE('Summary'[Amount],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg3")
    Unit3 = LOOKUPVALUE('Summary'[Unit],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg3")

     

     

    Regards,

    Charlie Liao

chefe's avatar
chefe
Icon for Helper II rankHelper II
10 years ago

Thank you for the quick reply! I forgot to mention, that I do not wish to physically create a new table. I wish to use this in-memory table as an interim step in a measure calculation.

alexpancakes's avatar
alexpancakes
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

Tried the same scenario with a number of DAX functions but didn't succeed either :(