Forum Discussion

karimkz's avatar
karimkz
Advocate I
9 years ago
Solved

create table from existing table

Hi all.  I'm getting used to DAX expressionswith columns, but not with tables.  I want to create new table for waterfall chart.  Here is the part of big table I have (1) and my target table (2).  ...
Need Help
Show and Tell
Tips and Tricks
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    karimkz

     

    hi. is possible that exists better solutions but i present one

     

    TableNew2 =
UNION (
    SELECTCOLUMNS (
        Table1,
        "UNIT", Table1[UNIT],
        "DATE", Table1[DATE],
        "FACTOR", "A",
        "WEIGHT", Table1[FACTOR A]
    ),
    SELECTCOLUMNS (
        Table1,
        "UNIT", Table1[UNIT],
        "DATE", Table1[DATE],
        "FACTOR", "B",
        "WEIGHT", Table1[FACTOR B]
    ),
    SELECTCOLUMNS (
        Table1,
        "UNIT", Table1[UNIT],
        "DATE", Table1[DATE],
        "FACTOR", "A",
        "WEIGHT", Table1[FACTOR C]
    )
)

    Or 

     

    TableNew =
UNION (
    SUMMARIZECOLUMNS ( Table1[UNIT], Table1[DATE], Table1[FACTOR A], "FACTOR", "A" ),
    SUMMARIZECOLUMNS ( Table1[UNIT], Table1[DATE], Table1[FACTOR B], "FACTOR", "B" ),
    SUMMARIZECOLUMNS ( Table1[UNIT], Table1[DATE], Table1[FACTOR C], "FACTOR", "C" )
)
MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

It can easily be done via the Query Editor, using Unpivot as illustrated in this video.

  • karimkz's avatar
    karimkz
    Advocate I
    9 years ago

    Thanks, you're awesome.

     

  • karimkz's avatar
    karimkz
    Advocate I
    9 years ago

    Actually, my columns are calculated columns, so Edit Query doesn't work here. 

    Any ideas?

    • Vvelarde's avatar
      Vvelarde
      Community Champion
      9 years ago

      karimkz

       

      hi. is possible that exists better solutions but i present one

       

      TableNew2 =
UNION (
    SELECTCOLUMNS (
        Table1,
        "UNIT", Table1[UNIT],
        "DATE", Table1[DATE],
        "FACTOR", "A",
        "WEIGHT", Table1[FACTOR A]
    ),
    SELECTCOLUMNS (
        Table1,
        "UNIT", Table1[UNIT],
        "DATE", Table1[DATE],
        "FACTOR", "B",
        "WEIGHT", Table1[FACTOR B]
    ),
    SELECTCOLUMNS (
        Table1,
        "UNIT", Table1[UNIT],
        "DATE", Table1[DATE],
        "FACTOR", "A",
        "WEIGHT", Table1[FACTOR C]
    )
)

      Or 

       

      TableNew =
UNION (
    SUMMARIZECOLUMNS ( Table1[UNIT], Table1[DATE], Table1[FACTOR A], "FACTOR", "A" ),
    SUMMARIZECOLUMNS ( Table1[UNIT], Table1[DATE], Table1[FACTOR B], "FACTOR", "B" ),
    SUMMARIZECOLUMNS ( Table1[UNIT], Table1[DATE], Table1[FACTOR C], "FACTOR", "C" )
)