Building relationship between data model


#1

I will like to seek you advise on how to build the relationship on data model. I have a customer count table as follow:-

Store code Store name Weekname Customer Count WeekofYear Year
101 ABC 4 Jan 2017 1000 1 2017

Customers count in the table above are by store, by week and weekly count from year 2017 to Mar 21, 2018.

I have another date table which I used the date table quote as published in Enterprise DNA.

I have issue on building the relationship between these 2 table as there is no unique identifier in the data table. Please advise how I should link up these 2 tables.

Please also advise on the measures for YTD & LY YTD customer count.

Thanks.


#2

When using a date table, you should always look to build a connection between the date column (in the date table) and a column in your fact table that has a date in it.

So in your customer count table you need to find the column that represents the date.

It would be a one to many relationship here also.

Certainly place a picture of the model once you give this a go and if it’s still not working.

Also certainly review this course for all the best practice tips on building effective data model. It’s a really crucial part of Power BI development


#3

Once you have your model setup, review this video for YTD and LYTD.

I’ve also included other content that you can use also. Lots to learn!