Forum Discussion

Kristofferaabo's avatar
Kristofferaabo
Helper IV
8 years ago
Solved

Summarize table

HI,   I would like to create a simple table based on another table, it must pull following columns where "Column A" should be uniques (like an excel pivot table)   Column A (unique values) Colun...
Need Help
Show and Tell
Tips and Tricks
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    Hi Kristofferaabo,

     

    To create a new table, in dax, you can use summarize(), selectcolumns(), addcolumns().

    To achieve distinct values in column A oin the calculate table, you can use summarize function like pattern below:

    calculate table =
UNION (
    SUMMARIZE (
        old_table1,
        old_table1[column_need_to_be_distinct],
        "column1", SUM ( old_table1[column1] )
    ),
    SUMMARIZE (
        old_table2,
        old_table2[column_need_to_de_distinct],
        "column2", SUM ( old_table2[column2] )
    )
)

    Also refer to DAX summarize here:https://msdn.microsoft.com/en-us/query-bi/dax/summarize-function-dax

     

    Regards,

    Jimmy Tao

  • Kristofferaabo's avatar
    Kristofferaabo
    Helper IV
    8 years ago

    Hi v-yuta-msft

    Thanks for your reply. No I'm nor interested to have duplicates in my new column (column A).

    This new table must only return distinct values from the other table

    Thanks

    Kristoffer

     

     

    • v-yuta-msft's avatar
      v-yuta-msft
      Community Support
      8 years ago

      Hi Kristofferaabo,

       

      To create a new table, in dax, you can use summarize(), selectcolumns(), addcolumns().

      To achieve distinct values in column A oin the calculate table, you can use summarize function like pattern below:

      calculate table =
UNION (
    SUMMARIZE (
        old_table1,
        old_table1[column_need_to_be_distinct],
        "column1", SUM ( old_table1[column1] )
    ),
    SUMMARIZE (
        old_table2,
        old_table2[column_need_to_de_distinct],
        "column2", SUM ( old_table2[column2] )
    )
)

      Also refer to DAX summarize here:https://msdn.microsoft.com/en-us/query-bi/dax/summarize-function-dax

       

      Regards,

      Jimmy Tao