Forum Discussion
(Another) Sort by another column problem
Trying to sort a column in my custom date table (a csv file) via a calculated column in the same table but am seeing an error. The calculated column does not reference the column I wish to sort by. Here's the DAX for the calculated column:
PeriodOffset = (Dates[Period]+(Dates[FiscalYear]*13))-
(CALCULATE(VALUES(Dates[Period]),
Dates[Date]=TODAY())+
(CALCULATE(VALUES(Dates[FiscalYear]),
Dates[Date]=TODAY())*13
))My date table has every date from 2003/4 to 2034/35, along with custom period numbers, calendar and fiscal years etc. The column I am trying to sort is called PeriodFiscalYear. Each value in that column has only one entry in the PeriodOffset column so it's not that.
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.
12 Replies
Anonymous -
What is the error you received?
Anonymous -
You've identified this table as a 'Mark as Date Table' correct? Can you provide a sample?
HI Anonymous ,
You can also take a look at the following blog about creating a custom sort order table to achieve customer sort order:
Regards,
Xiaoxin Sheng
