Sort by another column using dataset in another report?

[SamTrexler]

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

[v-piga-msft]

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

[SamTrexler]

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 Sequence	Code Value	Description
1	1	Initial Test
2	2	First Retest
3	3…n	Subsequent Retests
99	NULL, Blank, space or other	Catch-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 Sequence

Sort by DescriptionSort by Sequence and Description