Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

(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.
 
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 work around, I would just like to understand what is going on.
 
Thanks

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

12 REPLIES 12
v-shex-msft
Community Support
Community Support

HI @Anonymous ,

You can also take a look at the following blog about creating a custom sort order table to achieve customer sort order:

Custom Sorting in Power BI

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
ChrisMendoza
Resident Rockstar
Resident Rockstar

@Anonymous -

What is the error you received?






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Anonymous
Not applicable

Capture.PNG

 

@Anonymous -

You've identified this table as a 'Mark as Date Table' correct? Can you provide a sample?






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Anonymous
Not applicable

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.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Anonymous
Not applicable

@ChrisMendoza 

 

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.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Anonymous
Not applicable

@ChrisMendoza 

 

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.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Anonymous
Not applicable

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
Not applicable

Cheers Chris, I will. I'm off home now (in the UK) but will post back tomorrow.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.