cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Sort by another column using dataset in another report?

Is it possible to sort by another column if a report is linked to a dataset in another report?

 

I have a report that imports data, and many reports that use that dataset to analyze the data in different ways. In one of these reprots, I'd like to analyze by one column but sort by another column - a sequence number designed to order the report when this column is used. But the "Sort by column" icon is greyed out on the Modeling tab in Desktop - along with "New table", "New column", "New parameter", etc. So it seems it's not possible to sort on a column that's not in the visual, becuase the report is linked to a dataset in another report.

 

So I have tried adding the sequence column to the visuals. Depending on the visual I use, having the sequence number shown is at least confusing, and at worst cumbersome for the user. For example, in an area chart placing the sequence number first on the X axis and drilling down one level gives values like "1 Initial Test", "2 First Retest", etc. - not very friendly but not too bad. And they may never try to drill up or down, because this isn't really a "hierarchy" to the user even though the visual handles it that way.

 

But in a matrix report, having the highest level shown as this artificial sequence number is confusing, illogical and cumbersome. And there's no way to hide or avoid these totals at the top level, even though they are redundant with the level below and just provide a non-intuitive label that's used for sorting.

 

Reza Rad, in his blog post on sorting on a different column, suggests choosing "Hide in report view" (not specifically related to matrix reports). But this choice does not  appear in the context menu for the column, so that doesn't appear to be possible for this scenario.

 

Is there a solution to sort on another column when linking to a dataset in another report, without adding a higher hierarchy level  (or creating a hierarchy where none is needed) and showing this non-intuitive column?

 

Thanks in advance.

 

Sam

2 REPLIES 2
Highlighted
Resident Rockstar
Resident Rockstar

Re: Sort by another column using dataset in another report?

Hi @SamTrexler

 


 I have a report that imports data, and many reports that use that dataset to analyze the data in different ways. In one of these reprots, I'd like to analyze by one column but sort by another column - a sequence number designed to order the report when this column is used. But the "Sort by column" icon is greyed out on the Modeling tab in Desktop - along with "New table", "New column", "New parameter", etc. So it seems it's not possible to sort on a column that's not in the visual, becuase the report is linked to a dataset in another report.

 


 

From your description, it seems that you have a live connection of Power BI Service? If it is,  the Sort by column" icon is greyed out on the Modeling tab in Desktop - along with "New table", "New column", "New parameter", etc is because for live connection.

 

If you do get data by a live connection from Power BI Service, there is no option to sort by another column directly in Power BI Desktop.

 

You should sort the column in the regional report and republish to the Power BI Service, then you could have a live connection from Power BI Service.

 

If it is not your case, please show some screenshoots about your issue, so that we can help further investigate on it?

 

Reference:
Connect to datasets in the Power BI service from Power BI Desktop

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper IV
Helper IV

Re: Sort by another column using dataset in another report?

Hi @v-piga-msft.

 

Yes, you are correct - I am using a live connection. And the reference you provide seems to indicate this is a limitation by stating "Since this is a live connection, left-navigation and modeling are disabled". (It's not immediately clear that this includes "sort by column", since a few other items on the Modeling tab are available.)

 

We are an ISV, and our customers require us to use industry-standard descriptions - we can't change the text to control the sort order. So I've created a computed column to provide the desired sort:

 

Desired SequenceCode ValueDescription
11Initial Test
22First Retest
33…nSubsequent Retests
99NULL, Blank, space or otherCatch-all for tests not meaningful to this analysis

 

If I use the the sequence number column, I get the result shown in "Sorted by Sequence" - technically correct, but meaningless to our users.

 

If I use the description column, I get the result shown in "Sort by Description" - incorrect and misleading.

 

If I use both the sequence number and the description columns, I get the result shown in "Sort by Sequence and Description". The area graph may be okay, although they would not like seing the number in front of the description. But the first two levels of the hierarchy in the rows of the matrix report are showing the same data, with a meaningless label at the top level.

 

I can concatenate the sequence number and description so that the matrix report is similar to the area graph. But like the area graph, that is not ideal because it shows the number in front of the description.

 

Is there a way to get the sort I need, without showing this artificial sequence number at the front of the description? I would normally use "sort by column", but it's not practical to publish the dataset - almost 1GB in size - in each report where it is needed.

 

Thanks,

 

Sam

 

Sorted by SequenceSorted by Sequence

Sort by DescriptionSort by DescriptionSort by Sequence and DescriptionSort by Sequence and Description

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors