Create new table with one column that only consist of dates that exist in another table

Anonymous — Thu, 24 Jun 2021 08:09:52 GMT

Hi,

I am trying to create a new table in the data section of Power BI which only has 1 column called Date.

And this Date will only consist of dates from another main table which consist of several random dates.

May I know how do I do this?

Below is an illustration of what I'm trying to achieve:-

Main table

Sales	Amount	Date
Apple	50	02 March 2017
Orange	100	30 June 2018
Watermelon	20	30 June 2018

Create a new table that looks like this and contains only these 2 unique dates:

Date

02 March 2017

30 June 2018

I did try using this DAX:

Date =

RELATED(Main Table[Date])

but the error message says The column 'Main Table[Date]' either doesn't exist or doesn't have a relationship to any table available in the current context.

So when I tried to form a relationship between Main Table and New Table using One to One cardiniality, the error message says the cardiniality you selected isn't valid for this relationship.

Re: Create new table with one column that only consist of dates that exist in another table

Tanushree_Kapse — Thu, 24 Jun 2021 08:14:21 GMT

Hi Anonymous ,
Create a new table=
Calendar Table= DISTINCT(SELECTCOLUMNS(Main_Table,"Date",Main Table[Date]))

Hope this helps!

Re: Create new table with one column that only consist of dates that exist in another table

Anonymous — Thu, 24 Jun 2021 08:17:45 GMT

Tanushree_Kapse

Thanks! This works! Thank you so much for helping!

topic Create new table with one column that only consist of dates that exist in another table in DAX Commands and Tips

Create new table with one column that only consist of dates that exist in another table

Re: Create new table with one column that only consist of dates that exist in another table

Re: Create new table with one column that only consist of dates that exist in another table