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)

Colunm B (Can be first)

Column C (Can be first)

Column D (Can b first)

 

The purpose is to use this as a master table and thereby link other tables to its unique column A values

 

Thanks

Kristoffer

 

 

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

5 Replies