Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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
@Anonymous -
What is the error you received?
Proud to be a Super User!
@Anonymous -
You've identified this table as a 'Mark as Date Table' correct? Can you provide a sample?
Proud to be a Super User!
No, I haven't marked it as a date table. I don't need to use the time intelligence fucntions as my calendar has custom periods. I'd be happy to provide the csv but I don't have anywhere I can upload it that I can access as work.
@Anonymous -
Are they not custom periods based off of sequential dates? Those dates become the sort order. Time Intelligence functions do not need to be utilized just by marking as a date table.
I built a custom period calendar table, marked as date table, and have had no issues with sorting. Possibly it is just the implementation you've chosen that is causing intermittent successes.
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Cheers Chris, I will. I'm off home now (in the UK) but will post back tomorrow.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |