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.
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.
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.