Creating a key field

[ghammond09]

Hi,

This I suspect is very simple but for the life of me I can't find it.  I have imported a table of data into my PowerBi model.  One of the columns is a date field and in order to be able to link it to another table, I need to set the date field as a key.  Ths should (I hope) allow me to search the new table for data based on the date in the main date table I have.

Thanks,

[v-jianboli-msft · Accepted solution]

Hi @ghammond09 ,

If you want to link one table to another, please consider create relationship between the two tables.

For example:

Output:

For more details, please refer to:

Create and manage relationships in Power BI Desktop

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ghammond09]

I have a master Date table.  Works fine with other tables I have.

The new table is not sorting as I suspect I need a key field in it.  If so, how do I tag a column as a key field?

Thanks.

[amitchandak]

@ghammond09 , Not very clear. You can create a common date table and join with both the tables

key can be created as

key  = year([Date])*10000 + Month([Date])*100 + Day([Date])

example

Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])

)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.