Forum Discussion
Data view - date display formats
Thank you Anonymous and is 'YourFactTable' literally 'YourFactTable' ? or should that be the name of the table I am wanting to join to?
I feel like a baby, having to ask for each step, sorry.
I created the Calendar table, created a relationship between the 'Fact Table' and Calendar on the two date fields. The Calendar is the One side and the 'Fact Table' the Many. In my 'Fact Table' I created a new column: DateIndex = RELATED('Calendar'[Date]) which created ok but it is empty, no row has any data in it. What am I likely to have done wrong?
No worries. It is part of the learning process.
Folow the steps as follows
1. Create a Calendar / Date table is as
Calendar = CALENDAR (MINX('YourFactTable', [DateTime]),MAXX('YourFactTable', [DateTime]))
This will create the table Calendar in your data model with Date as a column.
2. Go the Modelling Tab in the power bi.
3. Go to Manage Relationship
4. Create relationship between the Date of Calendar Table and date column [DateTime] of YourFactTable
5. In YourFactTable create the column (which you might have already done)
DayDateTime = FORMAT([DateTime], "ddd DD/MM/YYYY hh:mm")
6. Now set the Sort order for DayDateTime as [DateTIme] column of yourfactable.
7. Now plot the DayDateTime it should be in the proper order.
If it works please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
Thank you for your step by step instructions Anonymous, I have followed them however still at step 6 I ge the error:
I can see what we are attempting to do - create a one to many relationship between the calendar table and the date field in the data table. So rows in the data table DayDateTime field will be sorted by just date in the Calendar table. Therefore Many rows in the data table will point to one row in the Calendar table.