mastertable based on groupby or summarize

[Kristofferaabo]

Hi,

I want to create a new table (Table A) based on colulms from another table (Table B).

In my table A I want only unique/distict values from Column A in Table B to be returned, like a pivot in excel.

How can I do that, I have tried with groupby and summarize, but now luck..

Thanks
Kristoffer

[Phil_Seamark]

Hi @Kristofferaabo

You can create a calculated table using the following syntax

New Table = ALL('Table B'[Column A])

Or

New Table = SUMMARIZECOLUMNS('Table B'[Column A])

[Kristofferaabo]

Hi @Phil_SeamarkI think I have tried this, but it wont return unique values.. For instance I column A was a country column, I only want United States to be returned once.

Do I do something wrong?

[Phil_Seamark · Accepted solution]

Hi @Kristofferaabo

Both should provide unique values.  I wonder if you have leading, or trailing spaces that need to be cleaned using TRIM 

eg, if I have Table B with the following data

Country

----------

A

A

A

B

B

The output of the DAX I gave you should be

Country

------------

A

B

I have attached a PBIX file

[cosminc]

Hi all,

i have seen your discussion and i have a similar situation but with one step further

on main table i have also a column like Sales; and i want to obtain a new table like yours, with unique values, but also with sum of Sales for each

is it posible?

Thanks,

Cosmin