Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
parag_lokare
Advocate I
Advocate I

PBIRS Jan2023 : TopN throwing error in Directquery report

We have a Direct Query Report running on PBIRS Jan2023. On the report we have a visual that displays top 20 values by a category. The Top 20 is set in the Filter Pane for the visual. Earlier to Jan2023, this was working fine. But after Jan2023 upgrade , the visual throws error "TopN with Ties clause is not allowed without a corresponding Orderby clause." 

We did refer to the Feature summary Power BI Report Server January 2023 Feature Summary | Microsoft Power BI Blog | Microsoft Power BI. The measure is already set to DistinctCount. 

 

The report has 3 datasets RV , AL and PS, The relationship is set as  RV (1:M) AL ( direction both) ,  RV (1:M) PS (direction both).

The visual has category and value from the AL dataset.

The visual does not throw any error until we add a report level filter (filter on all pages) from the PS dataset.

 

I captuared the query that was generated using SQLProfiler.  Below is the easy to understand version of that query. As you see there are two WithTies and the inner one is missing the orderby clause.

SELECT TOP (20) WITH TIES *
FROM (
SELECT TOP (20) WITH TIES [basetable0].[c12] ,COUNT_BIG(DISTINCT [a0]) AS [a0]
FROM (
SELECT v.VID [c2], CDSID [a0],AckBy [c12] FROM AL a
INNER JOIN RV v ON v.[VID] = a.[VID]
INNER JOIN PS p ON v.[VID] = p.[VID]
WHERE p.[PSID] = 1323
) [basetable0]
GROUP BY [basetable0].[c12]
) AS [MainTable]
ORDER BY [a0] DESC

1 REPLY 1
d_gosbell
Super User
Super User

If this stopped working after the upgrade it sounds like it might be a bug and you should raise a support ticked with Microsoft to get this investigated.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.