Data preview empty after filter + sort

[lukiz84]

Description

When I have a quite large table (20 cols, 300k rows) and click the preview data button then filter the first column (in my case a date column) and then click on the sort button, the table is empty.

It has been an issue for a long time, but I always forgot to report it.

Tabular Editor 3 Version

3.11

Screenshots

Steps to Reproduce

Open a large table in preview data, filter the first column, then sort it.

Expected behavior

The table should not be empty :D

Crash Report

No response

Windows Version

No response

[otykier]

Hi @lukiz84 I'm unfortunately not able to reproduce this in 3.12.0 (although I'm not aware of any changes to the Table Preview between 3.11 and 3.12).

• Is this on a DirectQuery table by any chance?
• Does it happen with any table/column or just certain ones?
• What is the data type of your [Berichtsdatum] column? From the screenshot it looks like a string, but that could just be the formatting settings...
• Is it only when you do a combination of filtering and sorting that the result is empty?

[lukiz84]

Hi. It has been there since forever I think.

1) No DirectQuery, connected live to SSAS on SQL Server 2019 (onprem) 2) When I use a very small table (I have one with 6 rows) the bug does NOT occur 3) Berichtsdatum is of type DateTime, FormatString dd.MM.yyyy - but it's a HIDDEN field - maybe that's the problem? 4) I just checked, thanks, no need to filter it, just sort by the hidden Berichtsdatum-Column and the table is empty

BR Lukas

[lukiz84]

I just tried another table with invisible columns and it's the same problem. When I sort by the hidden one, the table is empty afterwards

[otykier]

Could you let me know exactly how you are sorting the table? When I left-click on a column header or right-click and choose "Sort ascending" / "Sort descending" it works fine - even on hidden columns:

Also - how many rows are in the table?

Lastly, if it's not too much trouble, could you try to attach a SQL Profiler to your SSAS instance, to capture the queries generated by Tabular Editor (look for QueryBegin events). For example, the screenshot below shows the DAX query generated by Tabular Editor, when I've scrolled down to row number 52612 of the table and sorted by [Due Date] in ascending order:

If there's any error with the generated query, the SQL Profiler should also show you what the error is.

[lukiz84]

It doesn't matter how I sort. Usually i just click on the column header, but even with the context menu it blanks out the results afterwards.

I tested with a table with ~300k rows and one with just 67k rows, doesn't matter

Screenshot of SQL Profiler:

and

[otykier]

No errors in the profiler? What happens if you run this query directly in Tabular Editor?

EVALUATE TOPNSKIP(181, 0, 'SIS DR und Minusstunden',[MonthlyEmployeeRowID],ASC)

[lukiz84]

No errors in the BeginQuery part no, I just looked at them. But I think we get closer, because when I run the query in TE3 it throws an error:

[otykier]

Well there you have it :)

You have set "IsAvailableInMDX" to false for this column, which disables attribute hierarchies. However, this prevents TOPNSKIP from ordering by this column. We could probably do some kind of fallback to use TOPN when a column does not have attribute hierarchies available, but the issue with TOPN is that it is much, much slower than TOPNSKIP for large number of rows.

If you set IsAvailableInMDX back to true and perform a calculate refresh of the table, sorting within the table preview should work.

I'm not sure that we will treat this as a bug, due to the limitation with TOPNSKIP mentioned above. We could, however, disable sorting on such columns and show a message to inform the user why the column cannot be sorted.

[lukiz84]

thanks for your quick response! can be set to solved now