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

kcantor's avatar
kcantor
Icon for Community Champion rankCommunity Champion
10 years ago

chefe

Have you tried "Group by" in the Query Editor? It is on the Transform Tab and should provide the results you are seeking.

 

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

  • kcantor's avatar
    kcantor
    Icon for Community Champion rankCommunity Champion
    10 years ago

    Then why not just write up the SUM measure/calculation. Use the identifying code on the rows and it should create the totals you need by code. Then use that calculation within the next step.