I can't sort a table column by a calculated column

user_7715574 0
Viewed 568

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.

The weird thing is, I have had this working in a previous report. In this instance, I was simply trying to recreate the functionality but it won't do it. Even stranger, if I create the PeriodFiscalYear column as a calculated column (currently it's hard-coded in the csv file), it works! So I have a sort-of workaround, I would just like to understand what is going on.

Thanks

dax powerbi
2 Answers

I believe this has to do with the fact that data column are sorted when data are ingested into PBI. Calculated columns are calculated only at a later time.

Therefore:

  • you can sort data column only with other data columns (because calculated columns have not been calculated yet)
  • you can sort calculated column with both data column and calculated column

Solution:
A) PeriodFiscalYear becomes a calculated column
B) PeriodOffset becomes a data column (either in your CSV or Power Query)

user_86812290

I actually figured this out. The problem was with my data model - I had a circular relationship in there as I was deriving the Period column in one table using my calendar table then linking them back in the relationship!

I created a linking table with the keys in both to make the relationship, then hid it.

Thanks

user_77155740
Related