direct query

56 Topics

Evaluation of one of the rows caused an error (desktop OK)
Hello everyone! Could I get some help with the situation bellow: I have two Power BI reports, from different data models. -On report 1, I have a table of company stock by reference, also has the last movement for each material. -On report 2, I have a table for purchase orders by material. Both reports have updated data Now, I want to know in the purchase orders table the latest movement (if the material is in the stock table. If not, blank). For that, I have created a table using SUMMARIZE that contains only the material and last movement date. The table has no errors. I am importing this table to model 2 (direct query from power bi data model). I can see in model 2 no errors related to this table. I can see the data fine both desktop and online. No repeated references. Finally, I have created a column on the purchase orders table with the date. I have tried with both LOOKUPVALUE and MAXIMUM. In both cases, it works well on the desktop and crashes online with the error: "evaluation of one of the rows caused an error", referring to this column. Because I am going crazy, I have tried to import to model 2 directly the full stock table (was avoiding to reduce storage and duplicity). I get the same error. Thank you so much in advance!
Solved
mssilva
2 months ago Place Report Server
867Views
0likes
3Comments
Is it possible to use ODBC with Live(direct query with delegated user) in PBIRS
Is it possible to use ODBC with Live / DirectQuery using delegated user authentication in PBIRS? Since the ODBC connector supports Kerberos and OAuth, I’d like to know whether Power BI Report Server can leverage these authentication methods when connecting through this type of ODBC connector.
Solved
shoham39
7 months ago Place Report Server
1.8KViews
0likes
5Comments
SQL Query generation in Direct Query
Hi Team, we are generating the report by connecting to Databricks as the source. The tabe volumes are running into millions. The business ask is to have filters at date and few other columns. The slicers are being used. We have to use Direct Query to fetch the data as we have the RLS enablement as well at databricks level depending on the user who uses the report. We see the query generation using "IN" clause when we give a week filter. Can't we get this as data range "BETWEEN" clause instead? This would help to optimize the query to fetch the results in better response rate. We are unable to do that switch in the queries generated in direct query Kindly help
Solved
JothyGanesan
1 year ago Place Report Server
3.2KViews
2likes
9Comments
Power BI Report Server Direct Query from Spark and SQL Server database is not working
Hi Experts, I just tried using Power BI Desktop and get data in direct query mode from Spark/SQL Server with success. However, I uploaded the reports on my Power BI Report Server (September 2024) and tried to view the report then I get an error below. An unexpected error occurred. Please try again later. Please try again later or contact support. If you contact support, please provide these details. We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly. Request ID940c5bbb-a284-bb98-f3f4-17230d2b7b9c TimeWed Apr 23 2025 15:25:38 GMT+0700 (Indochina Time) Service version/powerbi/libs As searched on the community and see the PBI_SQL_TRUSTED_SERVERS variable, I am configuring the value as Localhost but the issue still persists. Please help. KL
Solved
kitti
1 year ago Place Report Server
4.2KViews
0likes
12Comments
Issue Direct Query with Report
Hello Everybody, We have a PBIRS 2025-01 (Version1.22.9153.7886). I create a report with a Direct Query connection to a SQL Server 2022. There is only 3 tables without relationship All works well in Power BI Desktop for Report Server (query retrieve data and visuals works well). I deployed report on server. When I try to open the report on the Server, there is the error message : When I go to Data Source all works well : We already add a enviornment variable PBI_SQL_TRUSTED_SERVERS on PBIRS with the SQL Server Source but nothing change. Logs on PBIRS shows a szucessful connection (code 200) without any error. Somebody can help me or have an idea to investigate ? Kind Regards, Thomas
Solved
ThomasRoulez
1 year ago Place Report Server
2.5KViews
0likes
9Comments
Calculation group issue PBIRS
So we recently updated our report server and desktop setup to January 2025 version and with it came a great bunch of new functionalities! As I was trying out the calculation groups functionally, I learned the hard way that it is not quite ready yet. If you have a model which is using direct query, then add a calculation group. The group automatically has storage mode "import", which makes the model a composite model. Which as far as I have understood is not supported for pbi report server(yet). So when I saved my report to the server, closed desktop and the next day tried to reopen my report I got the error message that my file is using a composite model and it's not supported in pbi report server. So I couldn't open it anymore, meaning I couldn't remove the calculation group. Meaning I had to grab an older version of my report and copy that data model into my new one and redo all of my work. I also tried opening it locally but it's the desktop version that doesn't support the model so that didn't help either. I don't have the option to open the report in a "regular" pbi desktop to fix it either. I think maybe the calculation groups option shouldn't be accessible if it basically breaks your dataset(with import data) if you are using direct query. I am not sure if this is a "bug" or if I as a user/developer should know about this but it seems to me like maybe the calculation groups setting should either be turned off or at least warn if you have a model using direct query storage mode. Petebro Let me know if there are any more specifications you need from me, but I assume this is a general thing. TLDR: PBI Report server + Direct query storage mode + adding calculation group = Bad
Solved
NiklasR
1 year ago Place Report Server
1.7KViews
2likes
3Comments
Failed to publish Power BI Report (DirectQuery + Gateway)
The issue: 1. When I'm attempting to publish the .pbix file (data load with direct query) through the Azure DevOps pipeline, the Power BI Service returns a BadRequest error. This error occurs even after updating the SQL Server and database parameters to the client’s specified gateway-compatible values. 2. I also notice that, the .pbix still reference the on-prem servers that I used to test locally, they exist in the data models. The thing is I cannot connect to client's SQL server from my device, so PowerBI desktop throws error that wouldn't let me apply the changes, save without apply didn't work. The old connections remain in the file. I tried with one BI report, where I change all the direct query into import mode, then it works and successfully published. But I'd better keep the direct query bc in some reports the dataset can be very large. Is there any way that I can update the Sql server / database parameters withouth actually connecting? Thank you for your time and attention.
Solved
Sue777
1 year ago Place Report Server
2.5KViews
0likes
7Comments
Power BI Desktop figures not equal in published in Workspace
I am working on a visual in Power BI desktop (value 3 with 1.41M). However, when I published it in the workspace (no saved bookmarks and no applied filters), the figures are different (value 3 with 1.37M). Values are normalization saved in Sharepoint while the count came from a direct query table from Snowflake. Any idea what happened here? Also when I tried import connection, the table looks the same between desktop and service.
Solved
ReidiLexi
2 years ago Place Report Server
712Views
0likes
2Comments
Error on Direct Query (Live) connection after upgrade from Jan 2024 to May 2024
I have an issue on direct query connections after the upgrade to May 2024 from Jan 2024. I only have a few like this. For the time being I've updated them to an import connections. The sources are just views, so I'm not sure why the error is saying Analysis Services. The sources are like this: let Source = Sql.Database("Server_Name", "Database_Name"), powerbi_source = Source{[Schema="dbo",Item="View_Name"]}[Data] in powerbi_source What I've tried: refreshing the connection in desktop and redeploying (works in desktop) changing the data source credentials on the report manager deploying to a test instance (same error) works from Power BI desktop both in May 2024 and Jan 2024 An unexpected error occurred. Please try again later. ErrorDialogHelperText We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly. Request ID 63e7a1a2-223a-61ee-76af-2b90359621aa Time Mon Jul 15 2024 12:15:43 GMT+1000 (Australian Eastern Standard Time) Service version /powerbi/libs
Solved
aduguid
2 years ago Place Report Server
1.9KViews
1like
1Comment
Error in report with Direct Query to MS SQL in Power BI Report Server 2024 May
After update from Power BI Report Server 2024 January there are error in all reports with Direct Query to MS SQL Error message in log like this 2024-06-07 14:06:25.9995|ERROR|4|PBI.Win.DataExtensionException|ExceptionType:Microsoft.PowerBI.DataExtension.Contracts.DataExtensionException;DataExtensionErrorDetails:Details: ErrorCode=QuerySystemError, ProviderErrorCode=0xC112001B, ErrorSource=PowerBI, Message=Failed to get CSDL., HResult=0x80004005, Language=en-US, ProviderErrorMessage=[<ccon>[content removed]</ccon>], ProviderGenericMessage=[Errors in the high-level relational engine. . The exception was raised by the IDbConnection interface.], ErrorSourceOrigin=MsolapWrapper, OnPremErrorCode=, InnerErrorDetails=[Type=MsolapWrapper.MsolapWrapperException, Message=Failure encountered while getting schema];stackTrace: в Microsoft.PowerBI.DataExtension.Msolap.SchemaCommand.GetModelMetadata(String catalogName, String perspectiveName, String supportedVersion, TranslationsBehavior translationsBehavior) в Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.<>c__DisplayClass10_0.<GetModelMetadata>b__0(IDbSchemaCommand command) в Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.ExecuteSchemaCommand[T](ExploreHostDataSourceInfo dataSourceInfo, IDbConnectionPool connectionPool, IConnectionFactory connectionFactory, IConnectionUserImpersonator connectionUserImpersonator, QueryExecutionOptionsBase queryExecutionOptions, ITelemetryService telemetryService, Func`2 action, String failTelemetryMessage, ServiceErrorStatusCode statusCode, ExploreBaseEvent exploreEvent);message:GetModelMetadata;| RequestID = 525e21e9-b08c-9d72-ada4-699b9333f5db ClientSessionID = 8c981325-95a5-c834-1d08-c0baa0d6da6e 2024-06-07 14:06:25.9995|ERROR|32|Failure in conceptualschema catalogObjectId=6539e747-5436-4812-a4dc-06a980b4bc0f| RequestID = 525e21e9-b08c-9d72-ada4-699b9333f5db ClientSessionID = 8c981325-95a5-c834-1d08-c0baa0d6da6e Microsoft.PowerBI.ReportingServicesHost.CannotRetrieveModelException: An error occurred while loading the model. Verify that the connection information is correct and that you have permissions to access the data source. ---> Microsoft.PowerBI.DataExtension.Contracts.DataExtensionException: Failed to get CSDL. ---> MsolapWrapper.MsolapWrapperException: Failure encountered while getting schema
Solved
AKAB
2 years ago Place Report Server
2.2KViews
0likes
2Comments