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
AJSPIN24
Frequent Visitor

Sorting matrix by date then value (creating dynamic rank)

Hi everyone,

I'm looking for advice on how to make a matrix more user friendly. It's a standard matrix with a project row and a variance column- see first picture. Project is a value from a table and variance is calculated using a measure.

Underneath each project row, users can drill down by date- see second picture.

The problem is, when a user drills down, they must also sort the matrix by project to make the dates to appear chronologically. If you sort the matrix by a field other than project, the dates are shown out of order. See the third picture for an example of this.

The functionality I'm hoping to achieve will allow users to sort by variance  and then drill down a row to see day by day variance in chronological order. I think this can be done by creating a dynamic rank column, that first sorts dates by ASC and then sorts variance by DESC. Users will then use the new rank column to properly sort the table.

(1) Unsorted Table:

AJSPIN24_4-1663253338103.png

(2) Same table as above but drilled down to date

AJSPIN24_3-1663253318094.png

(3) Same table as above but sorted by variance

AJSPIN24_2-1663253244181.png

Any advice would be greatly appreciated. Thank you!

1 ACCEPTED SOLUTION

@AJSPIN24 ,

 

My thought is maybe you could create two tables?One of them sorted by A and the other sorted by B, something like that.

 

Substituting your example would be equivalent to one table simply sorted by date and the other just sorted by variance.
If the user doesn't want to see the date, then he can just look at the data in the variances table.

 

Please let me know if I have misunderstood, thanks in advance~

 

Best regards,

Community Support Team Selina zhu

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

3 REPLIES 3
v-mengzhu-msft
Community Support
Community Support

Hi @AJSPIN24 ,

 

I can probably see what you mean, you want your matrix to be filterable by both conditions. But in my test, I don't think it's possible to achieve that. When I sort by column A, if I select column B again, then the order of column A will be invalid.

 

In another word , it seems that you can only sort by a certain column in the matrix.
This is my result, please let me know if there is any misunderstanding or mistake. Thanks in advance~

 

Best regards,

Community Support Team Selina zhu

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

Thanks for the reply- I think you're probably right.

 

If you can think of another way for me to present data in this manner, as a workaround to the ordering issue, please let me know. Users have specified to me that they don't want to see all dates unless they click/drill down, so a standard table visual wouldn't work either.

 

Thanks

@AJSPIN24 ,

 

My thought is maybe you could create two tables?One of them sorted by A and the other sorted by B, something like that.

 

Substituting your example would be equivalent to one table simply sorted by date and the other just sorted by variance.
If the user doesn't want to see the date, then he can just look at the data in the variances table.

 

Please let me know if I have misunderstood, thanks in advance~

 

Best regards,

Community Support Team Selina zhu

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

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.