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
lutho
Helper II
Helper II

Table visual sort order based on date column not displayed

Ok, I am feeling a bit foolish - this should not be hard.

 

I have a Table where I need to show the most recent 10 student results. If there are not enough in the present year I wil show prior year with a (year). I have written a measure and it all works. The problem is I need to sort these results by descending date order starting with the most recent result but not show the actual AssesmentRecordDate column. 

 

2020-12-31_19-47-16.jpg

 

What am I missing?

Thanks in advance, Lex

 

2 ACCEPTED SOLUTIONS
v-janeyg-msft
Community Support
Community Support

Hi,  @lutho 

 

It’s my pleasure to answer for you.

According to your description, I think you can use 'sort bu column' function in result column.

Like this:2.gif

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi, @lutho 

 

I think you need to classify the name of the same exam, for example science71, science72, so that there will be no errors.

 

Best Regards

Janey Guo

View solution in original post

16 REPLIES 16
v-janeyg-msft
Community Support
Community Support

Hi,  @lutho 

 

It’s my pleasure to answer for you.

According to your description, I think you can use 'sort bu column' function in result column.

Like this:2.gif

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Janey - brilliant - I knew there had to be an elegant solution. I took me a while to realise I had to select the results column and then do the sort by column and I have accepted this as the solution for others to leverage.

 

It did lead into the next minor challenge in that there are duplicate dates message where 2 exams were conducted on the same day. What would be the pro way to address this?

 

2021-01-06_12-14-57.jpg

And THANK YOU for your time and effort and skill.

 

 

Janey - my apologies - I am no longer able to accept the above solution as I replied. When you respond I will accept the solution before anything else.

Hi, @lutho 

 

I think you need to classify the name of the same exam, for example science71, science72, so that there will be no errors.

 

Best Regards

Janey Guo

Hi Janey, can you please elaborate on "Classify". I understand that classification is for data sensitivity and done in the Service?

The Example Display Result field I use is actually a measure: the Subject (English), the student test result (70) and IF not the current year a bracketed display of the year of the test.

I cannot find any options that make sense.

Hi, @lutho 

 

If you use measure, you can’t see the feature of ‘sort by column’, you should use calculated column, right? Is there only one column in your table visual? Can you share your formula and whether the same value appears in the result column?

 

Best Regards

Janey Guo

Hi Janey - that confirms why the column was not available for selection.

The table visual has only 1 column - recent history of exam results.

 

The DAX query is fairly simple (I am still getting my head around the ins and outs of Power BI):

 

Exam Display Result = SYN_AssessmentResults[Description] & " - " & SYN_AssessmentResults[AssessmentResult] & IF (SYN_AssessmentResults[Year]<>2020," (" & SYN_AssessmentResults[Year] & ")" )
 
The purpose of this measure was purely for the display in this one visual. If I need to make is a calculated table happy to do so if that sorts out my ordering issue. Only this measure is used in the table visual.

 

Hi, @lutho 

 

If you use a measure, then you put only one measure in table visual,it will only display one value instead of a list of values.

Are SYN_AssessmentResults[Description],SYN_AssessmentResults[AssessmentResult] and SYN_AssessmentResults[Year] all measures?

 

Best Regards

Janey Guo

Hi Janey, the 3 fields used in the measure are all proper table columns. Given this scenario is the best approach for me to create a calculated column for the "Exam Display Result"?

Warm regards, Lex

Hi, @lutho 

 

I think you are a little confused about the difference of measure and column. The measure changes with the context.

But no matter what, if you only want to put one column of values on table visual and sort by date, the measure can't be done, you should use column.

 

Best Regards

Janey Guo

Janey,

 

Thank you for everything.

 

All the best,

 

Lex

Hi Janes, I have run into an additional challenge. The method provided works as long as the data set I am trying to order/display does not contain duplicates. I even created a unique displaysortorder field across all exams. Unfortunately students sometimes get the same exam result - resulting in an error message that there are the same values for 2 different displaysortorder values. It seems illogical that PBI is enforcing this constraint. Thoughts?

amitchandak
Super User
Super User

@lutho , if it is measure, you sort asc and desc from three dots.

 

If it is column , the create

Most Recent Excam result 1 =[Most Recent Excam result 1]

 

Most Recent Excam result Sort = Switch( True() ,

[Most Recent Excam result 1] = "Accouunting ... " , 1 , // Write complete name

//Add others

,10

)

 

The sort Most Recent Excam result 1 on Most Recent Excam result sort and use that in visual

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

 

 

amitchandak
Super User
Super User

@lutho , First of check the data type of assessment date, is it date or not. It should be date; if not correct.

In three dots you have option to sort asc and desc. Click on the column name can also change the order.

Hi Amitchandrak, thanks for responding and perhaps I have confused the question by showing the AssessmentRecordDate in the image attached. My challenge is that what I actually need to show just the subject line aka:

 

2020-12-31_20-09-10.jpg

but sorted in the descending AssessmentDateOrder - without showing the actual date.

 

 

Hi,

Could you share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.