Forum Discussion
(Another) Sort by another column problem
I believe I've solved this problem. It was caused by a relationship between my date table and a fact table. The relationship was between a calculated column in the latter that looked up a value from a column in the former. Power BI allowed it, but it seemed to affect the behaviour of reports built using the dataset.
Removing the relationship means I can perform the sort I need.
To recreate the relationship, I've calculated a table in the query that include only the columns I need and inserted this between to the two tables in my model.
Are they not custom periods based off of sequential dates?
Yes, but the periods change slightly each financial year. For example, 29th April can be in Period 1 or Period 2. The logic for this is implemented.
Those dates become the sort order. Time Intelligence functions do not need to be utilized just by marking as a date table.
I wish to sort by Period/Fiscal Year for visuals. If I don't do this, the x-axis of a chart showing the last 13 periods of data would put period 13, 2018/19 after period 1, 2019/20. Hope that makes sense.
I can certainly try marking it as a date table, I'm just not sure why this would matter!
Anonymous -
Maybe take a look at how I implemented my custom periods at http://cjmendoza.yourweb.csuchico.edu/custom-calendar/create-a-custom-period-calendar-in-power-query/. Seems like your need is similar.
Cheers Chris, I will. I'm off home now (in the UK) but will post back tomorrow.
I've had a look at your post. Very similar to what I've done, except I did it in Excel then imported into Power BI. I think my PeriodOffset column is similar to your index column, except mine is dynamic; i.e. it's recalculated everytime the model is loaded. This means in time intelligence functions, I don't have to find the current period number each time: I know it's PeriodOffset = 0.
I'm still confused as to why my method is not sortable. Perhaps the way I am doing it isn't the best. I did try generating the column within the query, but I couldn't get the list functions to accept a table column as an argument.
Anonymous -
I would be interested in seeing your file. Hope you find your solution, best of luck.
I believe I've solved this problem. It was caused by a relationship between my date table and a fact table. The relationship was between a calculated column in the latter that looked up a value from a column in the former. Power BI allowed it, but it seemed to affect the behaviour of reports built using the dataset.
Removing the relationship means I can perform the sort I need.
To recreate the relationship, I've calculated a table in the query that include only the columns I need and inserted this between to the two tables in my model.